LEAP YEAR

[HighlandPiper]

Good afternoon all

Should my excel spreadsheet automatically identify 2024 as a leap year? It doesn't!

Let me expand on that. I have a lookup table that will calculate 36 months based on the start date.

Example
Manual input - Start Date: 02/02/2024
Formula - End Date: 02/02/2027

I am also using an online platform and when I enter the start date of 02/02/2024 the result is 03/02/2027

 

[Fluff]

Yes Excel does know that 2024 is a leap year. What makes you think it doesn't?

 

[Joe4]

What exactly is your formula?

 

[Joe4]

To prove that Excel recognizes the Leap Year, enter 2/2/24 in cell A1, then enter this formula in cell B1:

=A1+27

You can see that it returns 2/29/24

If you are trying to do date math where you are adding months or years to a date, it will ignore the day part and always return the same day, i.e.
regardless of whether or not there is a leap year, adding exactly 3 years to the date 2/2/24 is 2/2/27.

If you want it to be 2/1/27, you would need to add days, not months or years, i.e.

=A1+(365*3)

 

[Joe4]

I would add months, but I would use EDATE to get it:

=EDATE(B1,36)

Scott, that might be what they are using now, and if I understand it correctly, I think that is precisely what they are saying the problem is.
That formula would return 2/2/2027.

But if I am understanding it correctly, I think they are expecting wanting 2/1/2027 (because this year is a Leap Year, they are wanting one day earlier on the final date).
If their intention is to return 2/1/2027 and not 2/2/2027, they could not use a method like this that adds months or years, but would rather have to use one that add days, like I showed.

Based on the original question, that is my best guess, but I could be completely wrong, as they have not shown us the formula that they are currently using.

 

[Scott Huish]

But 365 isn't exactly accurate either. The 2nd example said they got a date of March 2, 2027 which made no sense at all to me.

This might be a little closer though:
=B1+(365.25*3)

If they decided they want 4 years instead of 3, your formula wouldn't give the result I would expect

Book1
	A	B	C	D
1	Manual input - Start Date	2/29/2024	2/28/2028	2/29/2028
2	Formula - End Date	2/28/2027
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1+(365*4)
D1	D1	=B1+(365.25*4)
B2	B2	=EDATE(B1,36)

EDATE doesn't always return the same day though.

If you do an EDATE on 2/29/24 with 36 month, it doesn't return March 1 which would technically be an equivalent day, It returns 2/28 since it's not a leap year

 

[Joe4]

But 365 isn't exactly accurate either. The 2nd example said they got a date of March 2, 2027 which made no sense at all to me.

This might be a little closer though:
=B1+(365.25*3)

I may have misunderstood what there question/end goal is. Without seeing their formula it is hard to tell.
Upon further review and looking back at the question, it looks like maybe 2/2/2027 is the desired goal, but they are getting 3/2/2027 (I missed that date at first, as being in the US, our date formats are m/d/yyyy, and I was focusing on the second number of 2 instead of the first number of 3).

So I am thinking that you are probably correct, and if 2/2/2027 is actually their goal, your EDATE formula would be my recommendation too.

 

[HighlandPiper]

Hi guys, thank you all for your replies, amazing! I will post my formula, an an extract from the lookup table tomorrow when I get home.

 

[HighlandPiper]

Yes Excel does know that 2024 is a leap year. What makes you think it doesn't?

Yes Excel does know that 2024 is a leap year. What makes you think it doesn't?

I thought it might; it will be my formula. I'll post it tomorrow. Thank you.

 

[HighlandPiper]

What exactly is your formula?

I'll post tomorrow. Thank you!