Assistance in updating formula for DATE functions - eg DateValue, Day, etc, for Leap Years

[matthewhinds]

To Whom It May Concern

I write to seek assistance in writing a formula for recognising and adjusting "DATE"-based formulas to include adjustments for Leap Years, please.

Please find attached an example spreadsheet of how I intend to use it.
Please note, Sheet Name: "Income & Cash or Debit Expenses", is the PRIMARY sheet; Cell A3, "Home and Utilities Sheet", is LINKED to the previous sheet.

"(IF(DAY('[Sheet Name]'!$A$3)<>365," etc, this refers to the number of days in a year (obviously); could anyone please show me how to write a formula that will automatically calculate and adjust for "leap years", ie 29 February 2024, 29 February 2028, 29 February 2032, and so on...

Please ignore:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

and

#REF!

Thank you, Matthew Hinds.

[sandy666]

try Power Query function:

Please Login or Register  to view this content.

result will be TRUE or FALSE
eg.

Date	LeapYear
21/02/2017	FALSE
21/02/2018	FALSE
21/02/2019	FALSE
21/02/2020	TRUE
21/02/2021	FALSE
21/02/2022	FALSE
21/02/2023	FALSE
21/02/2024	TRUE
21/02/2025	FALSE
21/02/2026	FALSE
21/02/2027	FALSE
21/02/2028	TRUE
21/02/2029	FALSE
21/02/2030	FALSE
21/02/2031	FALSE
21/02/2032	TRUE
21/02/2033	FALSE
21/02/2034	FALSE
21/02/2035	FALSE
21/02/2036	TRUE
21/02/2037	FALSE
21/02/2038	FALSE
21/02/2039	FALSE
21/02/2040	TRUE
21/02/2041	FALSE
21/02/2042	FALSE

[FlameRetired]

Similar to sandy's but with formula.

	A	B	C
1	1/1/1999	FALSE	In B1: =ISODD(MATCH(0,MOD(YEAR(A1),{400,100,4,1}),0))
2	1/1/2000	TRUE
3	1/1/2001	FALSE
4	1/1/2002	FALSE
5	1/1/2003	FALSE
6	1/1/2004	TRUE
7	1/1/2005	FALSE
8	1/1/2006	FALSE
9	1/1/2007	FALSE
10	1/1/2008	TRUE
11	1/1/2009	FALSE
12	1/1/2010	FALSE
13	1/1/2011	FALSE
14	1/1/2012	TRUE
15	1/1/2013	FALSE
16	1/1/2014	FALSE
17	1/1/2015	FALSE

[kvsrinivasamurthy]

IF(DAY('[Sheet Name]'!$A$3) gives the day of month. How it is compared to 365 is not clear.
If you want the last day of February in C3

Please Login or Register  to view this content.