Count the number of days between two dates that are in 2024

[slam]

I have a date in column Q (Start Date) & a date in column R (End Date) that are formatted as DD-MMM-YYYY, i.e. 03-APR-2024. The dates range from 2018 to 2027.

I need to count the number of days that are between the two dates that are in 2024. Some examples of what I'd want the output to be:

01-JAN-2018 | 31-DEC-2027 = 365
01-MAR-2024 | 31-MAR-2024 = 31
20-DEC-2023 | 02-JAN-2024 = 2

I don't really care if it accounts for leap years.

Could I trouble someone for a magical formula for this?

For Office 365.

Thanks!
<3

 

[Cubist]

I'm getting a #REF! on that - maybe because of my date format?

You might need to adjust the cell references to your start and end dates. Formatting shouldn't matter as long as it's a date type.

 

[slam]

Not sure what you mean. It seems to work here: (I am using YYYY-MM-DD formats):
Edit: I understand.. will update in a second. Although I didn't understand from your question you wanted zeros if the last date was prior to 2024.
What if both dates are after 2024?

If neither date is in 2024, the result should be 0.

Thanks!

 

[awoohaw]

If neither date is in 2024, the result should be 0.

Thanks!

SO your first example is wrong then?

Book1
	A	B	C	D	E	F
1	Start	End			Calc Start	Calc End
2	2018-01-01	2027-12-31	365	0.00	2024-01-01	2024-12-31
3	2024-03-01	2024-03-31	31	31.00	2024-03-01	2024-03-31
4	2023-12-20	2024-01-02	2	2.00	2024-01-01	2024-01-02
5	2020-01-25	2020-05-01		0.00	2024-01-01	2024-05-01
6	2020-06-15	2026-07-15		0.00	2024-01-01	2024-07-15
7	2028-09-25	2030-08-06		0.00	2024-01-01	2024-08-06
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(AND(YEAR(B2)<>2024,YEAR(A2)<>2024),0,DATE(2024,MONTH(B2),DAY(B2))-IF(YEAR(A2)=2024,A2,DATE(2024,1,1))+1)
E2:E7	E2	=IF(YEAR(A2)=2024,A2,DATE(2024,1,1))
F2:F7	F2	=DATE(2024,MONTH(B2),DAY(B2))

 

[Fluff]

Is it possible to get it to work with DD-MMM-YYYY?

It already does. Make sure that your dates are real dates & not text.

 

[slam]

You might need to adjust the cell references to your start and end dates. Formatting shouldn't matter as long as it's a date type.

Did do that.... but gave it one more try, and now it's working (so put that down to user error - me )

=SUMPRODUCT(--(YEAR(ROW(INDIRECT(S12&":"&T12)))=2024))

Thank you!!

Thank you to everyone. You are all amazing for the help you offer here!

 

[Fluff]

Glad you have a solution, but be aware that indirect is slow & volatile.