Substracting French entered dates when my system is in english

[Blanchetdb]

Hi

I am using an English system...

My workbook has a number of worksheets, English and French. I am able to calculate the difference between two dates in the English worksheets as the date is entered in an English format (April 1, 2022)....
the formula used is =D15-AH4 which is April 1, 2022 minus January 1, 2021.
The D15 cell is in a Text format which is needed as it copies to a Word document using another macro I have within my workbook
This process works

My French worksheets requires the person to enter the date in a French format (1 janvier, 2022) as Text in cell D15
the formula is the same and the cells are both formatted as "Date / 1 janvier, 2022 - French(Canada)"

the issue is that I get a #Value error due to the dates being written in French

I tested the cells by inserting the English format and it works but as soon as the date is entered in French using the month format

is there a way around that?
thanks

 

[Dave Patton]

What part of the formula yielded the error? Try Excel's Formula Evaluate!

You are using 4 characters for abbrev. I edited the table and formula.
Check help for Lookup. The data must be sorted Months Alpha.
I edited the formula and added the lookup table; check the data.
N.B Mai is 3 characters, note the ",". Are 3 character abbrev acceptable?
You can name the Lookup information see array in F15. I named the array aL for the formula in H15.

Date and Time.xlsm
	A	B	C	D	E	F	G	H
1	Month Alpha	Month #						French month text
2	aout	8
3	avri	4
4	dece	12
5	fevr	2
6	janv	1
7	juil	7
8	juin	6
9	mai,	5
10	mars	3
11	nove	11
12	octo	10
13	sept	9
14				1-Apr-22	Date	455
15				24 mars, 2022	Text	447	447	447
16				1 janvier, 2021	Text	0	0	0
17				15 mai, 2021	Text	134	134	134
24b

Cell Formulas
Range	Formula
F14	F14	=D14-AH4
F15:F17	F15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),{"aout",8;"avri",4;"dece",12;"fevr",2;"janv",1;"juil",7;"juin",6;"mai,",5;"mars",3;"nove",11;"octo",10;"sept",9}),LEFT(D15,2))-AH$4
G15:G17	G15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),$A$2:$B$13),LEFT(D15,2))-AH$4
H15:H17	H15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),aL),LEFT(D15,2))-AH$4

 

[Dave Patton]

N.B. It is a good idea to show the Excel version that you use in your profile.
XLookup is available in 365 and probably 2021.

You could use VLookup or XLookup with full month names.
Edit the formulas and extract the relevant text for month and refer to the relevant data for the lookup.
Ensure the spelling in the lookup information is correct.

Date and Time.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Month Alpha	Month #						French month text
2	aout	8		janvier	1
3	avri	4		février	2
4	dece	12		mars	3
5	fevr	2		avril	4
6	janv	1		mai	5
7	juil	7		juin	6
8	juin	6		juillet	7
9	mai,	5		août	8
10	mars	3		septembre	9
11	nove	11		octobre	10
12	octo	10		novembre	11
13	sept	9		décembre	12
14
15				24 mars, 2022	Text	447	447	447	447	447
16				1 janvier, 2021	Text	0	0	0	0	0
17				15 mai, 2021	Text	134	134	134	134	134
18
24b

Cell Formulas
Range	Formula
F15:F17	F15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),{"aout",8;"avri",4;"dece",12;"fevr",2;"janv",1;"juil",7;"juin",6;"mai,",5;"mars",3;"nove",11;"octo",10;"sept",9}),LEFT(D15,2))-AH$4
G15:G17	G15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),$A$2:$B$13),LEFT(D15,2))-AH$4
H15:H17	H15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),aL),LEFT(D15,2))-AH$4
I15:I17	I15	=DATE(RIGHT(D15,4),XLOOKUP(MID(D15,FIND(" ",D15)+1,FIND(",",D15)-1-FIND(" ",D15)),$D$2:$D$13,$E$2:$E$13),LEFT(D15,2))-AH$4
J15:J17	J15	=DATE(RIGHT(D15,4),VLOOKUP(MID(D15,FIND(" ",D15)+1,FIND(",",D15)-1-FIND(" ",D15)),$D$2:$E$13,2,0),LEFT(D15,2))-AH$4

 

[Blanchetdb]

What part of the formula yielded the error? Try Excel's Formula Evaluate!

You are using 4 characters for abbrev. I edited the table and formula.
Check help for Lookup. The data must be sorted Months Alpha.
I edited the formula and added the lookup table; check the data.
N.B Mai is 3 characters, note the ",". Are 3 character abbrev acceptable?
You can name the Lookup information see array in F15. I named the array aL for the formula in H15.

Date and Time.xlsm
	A	B	C	D	E	F	G	H
1	Month Alpha	Month #						French month text
2	aout	8
3	avri	4
4	dece	12
5	fevr	2
6	janv	1
7	juil	7
8	juin	6
9	mai,	5
10	mars	3
11	nove	11
12	octo	10
13	sept	9
14				1-Apr-22	Date	455
15				24 mars, 2022	Text	447	447	447
16				1 janvier, 2021	Text	0	0	0
17				15 mai, 2021	Text	134	134	134
24b

Cell Formulas
Range	Formula
F14	F14	=D14-AH4
F15:F17	F15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),{"aout",8;"avri",4;"dece",12;"fevr",2;"janv",1;"juil",7;"juin",6;"mai,",5;"mars",3;"nove",11;"octo",10;"sept",9}),LEFT(D15,2))-AH$4
G15:G17	G15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),$A$2:$B$13),LEFT(D15,2))-AH$4
H15:H17	H15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,4),aL),LEFT(D15,2))-AH$4

it works !!!!

again... thank you VERY much for all your help

 

[Dave Patton]

Thanks for the feedback.