calculate date difference

Question

I am looking for a formula to calculate the difference between two dates, while controlling for the year.&nbsp; I have a series of pairs of dates (A1,B1) (A2,B2)...&nbsp; The first date always precedes, or is the same as, the second date chronologically.&nbsp; The dates may occur before, during or after the year 2021.&nbsp; I am looking for a formula that will calculate the number of days that occur between the pairs, and only occur in 2021.&nbsp; I like the DATEDIF function, but will use whatever works.

riny_van_eekelen · Answer

rreiser68&nbsp;Think of it this way. How many days are there between January 1 and January 31? Without the +1, the formula just subtracts the (numeric) value for the start date from the value for the end date and returns 30. With the +1, the result equals 31.

mtarler · Answer

I believe the +1 is to include the start/end dates. So 2021-01-05 - 2021-01-03 would result in 2 (basically 5-3=2) but you actually have the 3rd, 4th, and 5th so the +1 is needed.

mtarler · Answer

the raw representation for dates is in days so basically just subtract the 2 dates. I don't understand the "while controlling the year" part.

riny_van_eekelen · Answer

rreiser68&nbsp;Perhaps this is what you need to calculate only the number of days that fall in 2021.=MAX(0,MIN(B1,DATE(2021,12,31))-MAX(A1,DATE(2021,1,1))+1)&nbsp;

rreiser68 · Answer

Riny_van_EekelenThis is VERY helpful. However, I don't quite understand why there is a +1 at then end of the formula. Could you possibly explain it to me?

Forum Discussion

calculate date difference

5 Replies

Resources