Forum Discussion
calculate date difference
I am looking for a formula to calculate the difference between two dates, while controlling for the year. I have a series of pairs of dates (A1,B1) (A2,B2)... The first date always precedes, or is ...
rreiser68 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)
- Riny_van_Eekelen
This 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?- 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.
rreiser68 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.
