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

 

[ExcelToDAX]

You can use the DATEDIF function. It doesn't show up in Excel's Intellisense, but it works. DATEDIF function - Microsoft Support

=DATEDIF(Start_date,End_date,"D")

 

[slam]

You can use the DATEDIF function. It doesn't show up in Excel's Intellisense, but it works. DATEDIF function - Microsoft Support

=DATEDIF(Start_date,End_date,"D")

I've been using that.... though if I just do =R2-Q2, it also defaults to days. The issue is I now need to only count the days that occur in 2024, and I'm not sure how to incorporate that.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2	01/01/2018	31/12/2027	366
3	01/03/2024	31/03/2024	30
4	20/12/2023	02/01/2024	2
Sheet6

Cell Formulas
Range	Formula
C2:C4	C2	=MIN(B2,DATE(2024,12,31))-MAX(A2,DATE(2023,12,31))

 

[awoohaw]

Try this formula:

Book1
	A	B	C	D
1
2	2018-01-01	2027-12-31	365	366.00
3	2024-03-01	2024-03-31	31	31.00
4	2023-12-20	2024-01-02	2	2.00
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=DATE(2024,MONTH(B2),DAY(B2))-IF(YEAR(A2)=2024,A2,DATE(2024,1,1))+1

 

[slam]

Hi Fluff - thanks for the reply. I see it working in your example, but it is not in mine; as it is showing 366 as every result, regardless of dates. I trust this is due to my different date format. Is it possible to get it to work with DD-MMM-YYYY?

Nice to see it automatically works for leap years though.

Thanks!

 

[Cubist]

Here's my attempt

Book1
	A	B	C	D
1	1/1/18	12/31/27	366
2	3/1/24	3/31/24	31
3	12/20/23	1/2/24	2
4
Sheet5

Cell Formulas
Range	Formula
C1:C3	C1	=SUMPRODUCT(--(YEAR(ROW(INDIRECT(A1&":"&B1)))=2024))

 

[slam]

Try this formula:

Book1
	A	B	C	D
1
2	2018-01-01	2027-12-31	365	366.00
3	2024-03-01	2024-03-31	31	31.00
4	2023-12-20	2024-01-02	2	2.00
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=DATE(2024,MONTH(B2),DAY(B2))-IF(YEAR(A2)=2024,A2,DATE(2024,1,1))+1

This doesn't seem to be working when both dates are outside 2024. I.e., for 22-May-2021 to 31-Dec-2023 it is showing 366 when it should be showing 0.

 

[slam]

Here's my attempt

Book1
	A	B	C	D
1	1/1/18	12/31/27	366
2	3/1/24	3/31/24	31
3	12/20/23	1/2/24	2
4
Sheet5

Cell Formulas
Range	Formula
C1:C3	C1	=SUMPRODUCT(--(YEAR(ROW(INDIRECT(A1&":"&B1)))=2024))

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

 

[awoohaw]

This doesn't seem to be working when both dates are outside 2024. I.e., for 22-May-2021 to 31-Dec-2023 it is showing 366 when it should be showing 0.

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?