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

 

[jasonb75]

Enter the dates in english (your local format) but format them as french and it should work fine, as you can see in the example below the TODAY() function is used to produce the short date in A1 and the other dates below are taken from that then formatted in UK English and Canadian French long date formats respectively.

Book1
	A
1	29/11/2021
2	29 November 2021
3	29 novembre, 2021
Sheet1

Cell Formulas
Range	Formula
A1	A1	=TODAY()
A2:A3	A2	=A1

 

[Blanchetdb]

Enter the dates in english (your local format) but format them as french and it should work fine, as you can see in the example below the TODAY() function is used to produce the short date in A1 and the other dates below are taken from that then formatted in UK English and Canadian French long date formats respectively.

Book1
	A
1	29/11/2021
2	29 November 2021
3	29 novembre, 2021
Sheet1

Cell Formulas
Range	Formula
A1	A1	=TODAY()
A2:A3	A2	=A1

Enter the dates in english (your local format) but format them as french and it should work fine, as you can see in the example below the TODAY() function is used to produce the short date in A1 and the other dates below are taken from that then formatted in UK English and Canadian French long date formats respectively.

Book1
	A
1	29/11/2021
2	29 November 2021
3	29 novembre, 2021
Sheet1

Cell Formulas
Range	Formula
A1	A1	=TODAY()
A2:A3	A2	=A1

thank you for responding but I don't fully understand what you are proposing ... let me clarify my issue

Cell D15 --> cell formatted as Text / French (Canada) ... user types the date as follows 1 avril, 2022
Cell AH4 --> cell contains date 1 janvier, 2021 ... formatted as Date / French (Canada) .... this date never changes
I require the date difference in days between the two French dates to appear in cell AG4 .... this is where I am getting the #VALUE
error message

thanks

 

[jasonb75]

Cell D15 --> cell formatted as Text / French (Canada) ... user types the date as follows 1 avril, 2022

An english version of excel will never read this. All dates need to be entered as proper dates and formatted as required.

1 avril, 2022 needs to be entered as 01/04/2022 (assuming dmy regional settings) then formatted by choosing French (canadian) from the Locale dropdown on the date section of the number format tab (ctrl 1).

 

[Dave Patton]

If the data consists of dates, the arithmetic will work see F14.
If the data is text, convert the amounts to dates and then subtract see F15.
My first thought is Lookup. You can build a table or put all the month names in the formula.

Date and Time.xlsm
	D	E	F
14	01 April, 2022	Date	455
15	1 avril, 2022		455
24b

Cell Formulas
Range	Formula
F14	F14	=D14-AH4
F15	F15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,3),{"avr",4;"jan",1}),LEFT(D15,1))-AH4

 

[Blanchetdb]

If the data consists of dates, the arithmetic will work see F14.
If the data is text, convert the amounts to dates and then subtract see F15.
My first thought is Lookup. You can build a table or put all the month names in the formula.

Date and Time.xlsm
	D	E	F
14	01 April, 2022	Date	455
15	1 avril, 2022		455
24b

Cell Formulas
Range	Formula
F14	F14	=D14-AH4
F15	F15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,3),{"avr",4;"jan",1}),LEFT(D15,1))-AH4

awesome!!! it works ....

I have to admit that I do not fully understand the formula but will most definitely do some studying to get the full scope of it. I added the months to the formula but I am encountering one issue.

If I enter 1 janvier, 2021 in D15, I should get a difference of "0" but I am getting "31" .... is there a spot in the formula that it is adding a full month to the count?

 

[Blanchetdb]

awesome!!! it works ....

I have to admit that I do not fully understand the formula but will most definitely do some studying to get the full scope of it. I added the months to the formula but I am encountering one issue.

If I enter 1 janvier, 2021 in D15, I should get a difference of "0" but I am getting "31" .... is there a spot in the formula that it is adding a full month to the count?

just fixed it .... started "jan" as "0" instead of 1 and continued from there

anyway .... thanks a lot

 

[Dave Patton]

check for extraneous spaces in the data
I added an example with a lookup table. You may want to add the other months.

try Excel's Formula Evaluate Formula to review the logic

Date and Time.xlsm
	D	E	F	G
1		Month Alpha	Month #
2		Avr	4
3		Jan	1
4
5
6
7
8
9
10
11
12
13
14	01 April, 2022	Date	455
15	1 Janvier, 2021		0	0
16	1 avril, 2022		455	455
24b

Cell Formulas
Range	Formula
F14	F14	=D14-AH4
F15	F15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,3),{"avr",4;"jan",1}),LEFT(D15,1))-AH4
G15:G16	G15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,3),$E$2:$F$3),LEFT(D15,1))-AH$4
F16	F16	=DATE(RIGHT(D16,4),LOOKUP(MID(D16,FIND(" ",D16)+1,3),{"avr",4;"jan",1}),LEFT(D16,1))-AH4

 

[Blanchetdb]

check for extraneous spaces in the data
I added an example with a lookup table. You may want to add the other months.

try Excel's Formula Evaluate Formula to review the logic

Date and Time.xlsm
	D	E	F	G
1		Month Alpha	Month #
2		Avr	4
3		Jan	1
4
5
6
7
8
9
10
11
12
13
14	01 April, 2022	Date	455
15	1 Janvier, 2021		0	0
16	1 avril, 2022		455	455
24b

Cell Formulas
Range	Formula
F14	F14	=D14-AH4
F15	F15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,3),{"avr",4;"jan",1}),LEFT(D15,1))-AH4
G15:G16	G15	=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,3),$E$2:$F$3),LEFT(D15,1))-AH$4
F16	F16	=DATE(RIGHT(D16,4),LOOKUP(MID(D16,FIND(" ",D16)+1,3),{"avr",4;"jan",1}),LEFT(D16,1))-AH4

I can't get it to work with the proper count...

the cell that contains January 1, 2021 (AH4), what is formatted as? cell AH4 will always be January 1, 2021 but D15 can change to any date of the calendar but must be in a Text format and as follows i.e. 24 mars, 2022

 

[Blanchetdb]

I can't get it to work with the proper count...

the cell that contains January 1, 2021 (AH4), what is formatted as? cell AH4 will always be January 1, 2021 but D15 can change to any date of the calendar but must be in a Text format and as follows i.e. 24 mars, 2022

this is what I wrote:
=DATE(RIGHT(D15,4),LOOKUP(MID(D15,FIND(" ",D15)+1,3),{"avri",3;"janv",1;"févr",2;"mars",3;"mai ",5;"juin",6;"juil",7;"aout",8;"sept",9;"octo",10;"nove",11;"déce",12}),LEFT(D15,1))-AH4