Add and subtract the differance between two dates

[Neokey]

Hi all,

I have a workbook where I need to show the difference between two dates in months and days, where one date can be earlier in column A and B the date can be later in column A and B.

A	B	A & B Diff
03/08/2023	14/06/2024	10M 11D
04/05/2024	14/12/2023	#NUM!​
22/11/2023	08/03/2024	3M 15D
25/01/2024	24/11/2023	#NUM!​
04/08/2023	14/12/2023	4M 10D
11/01/2024	30/05/2024	4M 19D
10/01/2024	30/05/2024	4M 20D
24/01/2024	07/06/2024	4M 14D
16/02/2024	26/06/2024	4M 10D
24/02/2024	27/10/2023	#NUM!​

I need to show negative months and days where B is earlier than A. can anyone help please.

Thank you

 

[Shortmeister1]

Something like this:

=IFERROR((DATEDIF(0,D2,"y") & " years " & DATEDIF(0,D2,"ym") & " months "& DATEDIF(0,D2,"md") &" days"), 
"- " & (DATEDIF(0,-D2,"y") &" years " & DATEDIF(0,-D2,"ym") & " months " & DATEDIF(0,-D2,"md") & " days"))

 

[duggie33]

I thought I had a good solution with DATEDIF, but it seems to miscalculate around leap day 2024. Does anyone know why this happens?

Book1
	A	B	C	D	E	F
10	Start	End	Month Diff	Day Diff	Concat	Check
11	2023-Aug-3	2024-Jun-14	10	11	10M 11D	2024-Jun-14
12	2024-May-4	2023-Dec-14	-4	-20	-4M 20D	2023-Dec-15
13	2023-Nov-22	2024-Mar-8	3	15	3M 15D	2024-Mar-8
14	2024-Jan-25	2023-Nov-24	-2	-1	-2M 1D	2023-Nov-24
15	2023-Aug-4	2023-Dec-14	4	10	4M 10D	2023-Dec-14
16	2024-Jan-11	2024-May-30	4	19	4M 19D	2024-May-30
17	2024-Jan-10	2024-May-30	4	20	4M 20D	2024-May-30
18	2024-Jan-24	2024-Jun-7	4	14	4M 14D	2024-Jun-7
19	2024-Feb-16	2024-Jun-26	4	10	4M 10D	2024-Jun-26
20	2024-Feb-24	2023-Oct-27	-3	-28	-3M 28D	2023-Oct-27
21	2024-Mar-1	2023-Dec-14	-2	-16	-2M 16D	2023-Dec-16
22	2024-Feb-1	2023-Dec-14	-1	-18	-1M 18D	2023-Dec-14
23	2024-Feb-29	2023-Dec-14	-2	-15	-2M 15D	2023-Dec-14
Sheet1

Cell Formulas
Range	Formula
C11:C23	C11	=IFERROR(DATEDIF(A11,B11,"m"),-1*DATEDIF(B11,A11,"m"))
D11:D23	D11	=IFERROR(DATEDIF(DATE(YEAR(A11),MONTH(A11)+C11,DAY(A11)),B11,"d"),-1*DATEDIF(DATE(YEAR(B11),MONTH(B11)-C11,DAY(B11)),A11,"d"))
E11:E23	E11	=C11&"M "&ABS(D11)&"D"
F11:F23	F11	=DATE(YEAR(A11),MONTH(A11)+C11,DAY(A11)+D11)

Doug

 

[duggie33]

Yeah, for sure there is something off with the DATEDIF function and leap years. I do not fully comprehend it, but here are some references.

DATEDIF Worksheet Function

www.cpearson.com

A quote from that page..."Since Date1 is not a leap year, the date 29-Feb is not counted."

Where I found a link to that:

Question on DATEDIF with Leap Years consideration

Hi, Am I correct to say these.... If I do a datedif between say 01 Jan 1900 and 01 Jan 2010, I should deduct one day. Like wise if I do a datedif between 01 Jan 1800 and 01 Jan 2010, I should deduct two days. If I do a datedif between 01 Mar 1900 and 01 Jan 2010, the result is correct ?

www.mrexcel.com

Some strange results related to leap year calculations.

Book1
	A	B	C	D	E	F
25	2024-Mar-2	2023-Dec-30	-2	-1	-2M 1D	2024-Jan-1
26	2024-Mar-2	2023-Dec-31	-2	0	-2M 0D	2024-Jan-2
27	2024-Mar-2	2024-Jan-1	-2	-1	-2M 1D	2024-Jan-1
Sheet1

Cell Formulas
Range	Formula
C25:C27	C25	=IFERROR(DATEDIF(A25,B25,"m"),-1*DATEDIF(B25,A25,"m"))
D25:D27	D25	=IFERROR(DATEDIF(DATE(YEAR(A25),MONTH(A25)+C25,DAY(A25)),B25,"d"),-1*DATEDIF(DATE(YEAR(B25),MONTH(B25)-C25,DAY(B25)),A25,"d"))
E25:E27	E25	=C25&"M "&ABS(D25)&"D"
F25:F27	F25	=DATE(YEAR(A25),MONTH(A25)+C25,DAY(A25)+D25)

 

[Neokey]

Thank you for your help and it was so close to a solution.

 

[duggie33]

If you can work with the number of days between dates, this will work...

Book1
	A	B	C
30	2024-Mar-2	2023-Dec-30	-63
31	2024-Mar-2	2023-Dec-31	-62
32	2024-Mar-2	2024-Jan-1	-61
Sheet1

Cell Formulas
Range	Formula
C30:C32	C30	=B30-A30

 

[Dave Patton]

Dates Time.xlsm
	A	B	C	D
1	Start	End	Month Diff	Days
2	3-Aug-23	14-Jun-24	10	11
3	4-May-24	14-Dec-23	4	20
4	22-Nov-23	8-Mar-24	3	15
5	25-Jan-24	24-Nov-23	2	1
6	4-Aug-23	14-Dec-23	4	10
7	11-Jan-24	30-May-24	4	19
8	10-Jan-24	30-May-24	4	20
9	24-Jan-24	7-Jun-24	4	14
10	16-Feb-24	26-Jun-24	4	10
11	24-Feb-24	27-Oct-23	3	28
12	1-Mar-24	14-Dec-23	2	16
13	1-Feb-24	14-Dec-23	1	18
14	29-Feb-24	14-Dec-23	2	15
15
3g

Cell Formulas
Range	Formula
C2:C14	C2	=DATEDIF(MIN(A2:B2),MAX(A2:B2),"m")
D2:D14	D2	=ABS(B2-A2)-(EDATE(MIN(A2:B2),C2)-MIN(A2:B2))