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

 

[Asbestos_Jen]

The 2nd example said they got a date of March 2, 2027 which made no sense at all to me.

I despise all numerical dates for this reason, and try to always change dates to display as 2-Feb-24. There's rarely any confusion.

 

[HighlandPiper]

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)

This is my problem Joe, I am using a formula to calculate 3 years exactly. I need the formula to calculate 3 years whether that includes a leap year or not.

For this example I would be looking for 02/02/24 which should be 03/02/2027

 

[Joe4]

For this example I would be looking for 02/02/24 which should be 03/02/2027

Thanks does not make any sense. Three years from 2/2/24 is 2/2/27, regardless of whether there is a leap year or not.
If anything, if you are treating 365 days as a full year, then the date would be 1/2/27, not 3/2/27.
So even if you are trying to account for that, you are going the wrong way.

If you think otherwise, please explain your logic in plain English. Walk us through (justify) how you expect to get to the date 3/2/27.
No matter how you define a year, 3/2/27 is either:
- 3 years and 1 day from 2/2/24
or
- 3 years and 2 days from 2/2/24

Exactly three years from 2/2/24 would be either 1/2/27 or 2/2/27, depending on whether you are assuming 365 days in a year, or just by adding exactly 3 years to the year portion of the date, leaving month and day alone.

 

[HighlandPiper]

Hi all

Unfortunately, I haven't got access to the file with the dates I supplied earlier but I do have the same scenario with different dates. I have attached an image showing an extract from the spreadsheet along with the formula. I have also included a jpg with additional information. Basically the formula I am using on my spreadsheet is 1 day out from the online platform date and I don't understand why. I thought it may be an issue with 2024 being a leap year, but I now know that is not the case. I am by no means a competent excel user nor am I a maths guru. This is beyond me! Any help you can offer would be good.

 

[HighlandPiper]

I am looking at the online platform expecting that to be correct, but based on what you just said excel is correct.

 

[Joe4]

So is there still a question here for us, or are you all set now (at least from an Excel standpoint)?

 

[HighlandPiper]

So is there still a question here for us, or are you all set now (at least from an Excel standpoint)?

I think I have the answer I need.

 

[HighlandPiper]

Thank you all for your input. I do apologise for not being clear in the beginning. I think I need to take my query elsewhere outside of Excel.