Modifying Formula to Show Days Till Next Presidential Election Day.

[Sh8dyDan]

I need some help figuring out how to modify these two formulas. They work as expected. Instead of showing a blank when not election or inauguration year I'd like to show days till the next date.

Presidential Election Day

=IF(MOD(YEAR(Timesheet!$E$22),4)=0,DATE(YEAR(Timesheet!$E$22),11,6)-(WEEKDAY(DATE(YEAR(Timesheet!$E$22),11,6))-2)+1,"")

Presidential Inauguration Day

=IF(MOD(YEAR(Timesheet!$E$22)-1,4)=0,DATE(YEAR(Timesheet!$E$22),1,20)+CHOOSE(WEEKDAY(DATE(YEAR(Timesheet!$E$22),1,20)),1,0,0,0,0,0,0),"")

 

[Sh8dyDan]

The Presidential Election Day formula will return the date for Tuesday after the first Monday of November for every year divisible by four. If the year is not divisible by four it will be FALSE and blank. Instead of showing blank, I'd like it to show days remaining until the formula is TRUE.

Dates are in M/D/Y format.

2023 Paycheck Calculator - V2 - 07.xlsx
	E
21	Start Date
22	10/7/2023
Timesheet

2023 Paycheck Calculator - V2 - 07.xlsx
	D	E	F	G	H	I	J	K	L	M
58	Presidential Election Day	Observed	US	Tuesday after first Monday of November	Public	Days till TRUE		🗳️		🗳️
59	Presidential Inauguration Day	Observed	US	20th day of January	Federal	Days till TRUE		✋		✋
Holidays

Cell Formulas
Range	Formula
I58	I58	=IF(MOD(YEAR(Timesheet!$E$22),4)=0,DATE(YEAR(Timesheet!$E$22),11,6)-(WEEKDAY(DATE(YEAR(Timesheet!$E$22),11,6))-2)+1,"Days till TRUE")
K58,M58	K58	=IF($P58=TRUE,"🗳️ ","")
E58:E59	E58	=IF($P53=TRUE,"Observed","Not Observed")
I59	I59	=IF(MOD(YEAR(Timesheet!$E$22)-1,4)=0,DATE(YEAR(Timesheet!$E$22),1,20)+CHOOSE(WEEKDAY(DATE(YEAR(Timesheet!$E$22),1,20)),1,0,0,0,0,0,0),"Days till TRUE")
J59,L59	J59	=IF(MOD(YEAR(Timesheet!$E$22)-1,4)=0,DATE(YEAR(Timesheet!$E$22),1,20)+CHOOSE(WEEKDAY(DATE(YEAR(Timesheet!$E$22),1,20)),1,0,0,0,0,0,0),"")
K59,M59	K59	=IF($P59=TRUE,"✋ ","")

 

[Sh8dyDan]

2023 Paycheck Calculator - V2 - 07.xlsx
	E
21	Start Date
22	10/7/2024
Timesheet

2023 Paycheck Calculator - V2 - 07.xlsx
	D	E	F	G	H	I	J	K	L	M
58	Presidential Election Day	Observed	US	Tuesday after first Monday of November	Public	Tuesday, November 5, 2024		🗳️		🗳️
59	Presidential Inauguration Day	Observed	US	20th day of January	Federal	Days till TRUE		✋		✋
Holidays

Cell Formulas
Range	Formula
I58	I58	=IF(MOD(YEAR(Timesheet!$E$22),4)=0,DATE(YEAR(Timesheet!$E$22),11,6)-(WEEKDAY(DATE(YEAR(Timesheet!$E$22),11,6))-2)+1,"Days till TRUE")
K58,M58	K58	=IF($P58=TRUE,"🗳️ ","")
E58:E59	E58	=IF($P53=TRUE,"Observed","Not Observed")
I59	I59	=IF(MOD(YEAR(Timesheet!$E$22)-1,4)=0,DATE(YEAR(Timesheet!$E$22),1,20)+CHOOSE(WEEKDAY(DATE(YEAR(Timesheet!$E$22),1,20)),1,0,0,0,0,0,0),"Days till TRUE")
J59,L59	J59	=IF(MOD(YEAR(Timesheet!$E$22)-1,4)=0,DATE(YEAR(Timesheet!$E$22),1,20)+CHOOSE(WEEKDAY(DATE(YEAR(Timesheet!$E$22),1,20)),1,0,0,0,0,0,0),"")
K59,M59	K59	=IF($P59=TRUE,"✋ ","")

2023 Paycheck Calculator - V2 - 07.xlsx
	E
22	10/7/2025
23	10
Timesheet

Cell Formulas
Range	Formula
E23	E23	=IF($AI$6=1,10,IF($AI$6=2,12,))

2023 Paycheck Calculator - V2 - 07.xlsx
	D	E	F	G	H	I	J	K	L	M
58	Presidential Election Day	Observed	US	Tuesday after first Monday of November	Public	Days till TRUE		🗳️		🗳️
59	Presidential Inauguration Day	Observed	US	20th day of January	Federal	Monday, January 20, 2025	Monday, January 20, 2025	✋	Monday, January 20, 2025	✋
Holidays

Cell Formulas
Range	Formula
I58	I58	=IF(MOD(YEAR(Timesheet!$E$22),4)=0,DATE(YEAR(Timesheet!$E$22),11,6)-(WEEKDAY(DATE(YEAR(Timesheet!$E$22),11,6))-2)+1,"Days till TRUE")
K58,M58	K58	=IF($P58=TRUE,"🗳️ ","")
E58:E59	E58	=IF($P53=TRUE,"Observed","Not Observed")
I59	I59	=IF(MOD(YEAR(Timesheet!$E$22)-1,4)=0,DATE(YEAR(Timesheet!$E$22),1,20)+CHOOSE(WEEKDAY(DATE(YEAR(Timesheet!$E$22),1,20)),1,0,0,0,0,0,0),"Days till TRUE")
J59,L59	J59	=IF(MOD(YEAR(Timesheet!$E$22)-1,4)=0,DATE(YEAR(Timesheet!$E$22),1,20)+CHOOSE(WEEKDAY(DATE(YEAR(Timesheet!$E$22),1,20)),1,0,0,0,0,0,0),"")
K59,M59	K59	=IF($P59=TRUE,"✋ ","")

 

[Peter_SSs]

I'm guessing that in I59 of the first mini sheet and I58 of the second mini sheet you don't actually want the text "Days till TRUE" but some other value. What I mean by ..

concrete examples of the actual expected results for both formulas

.. is
what actual value do you want in I59 of the first mini sheet for 10/7/2024 in E22 of Timesheet, and
what actual value do you want in I58 of the second mini sheet for 10/7/2025 in E22 of Timesheet?

 

[Sh8dyDan]

The days left when the formula will be TRUE again 4 years in the future from E22 on Timesheet in 2028.

 

[Peter_SSs]

The days left ..

Concrete answer would mean: How many days are there left for each of the earlier examples?

 

[Sh8dyDan]

Good Night.

 

[Sh8dyDan]

Book1
	D	E	F
5		Future	Days
6	E22	4 Years	Till
7	10/7/2024	10/7/2028	1461
Sheet1

Cell Formulas
Range	Formula
F7	F7	=E7-D7

 

[Dave Patton]

With the date now Oct 7 2024, consider the following

T202401a.xlsm
	D	E	F	G	H	I	J
1					Days to election	2024	Tue Nov 5, 2024
2						2028	Tue Nov 7, 2028
3						2032	Tue Nov 2, 2032
4						2036	Tue Nov 4, 2036
5				Next	Subsequent	2040	Tue Nov 6, 2040
6		07-Oct-23	Tue 05-Nov-24	395	1858	2044	Tue Nov 1, 2044
7		07-Oct-24	Tue 05-Nov-24	29	1492
8		30-Sep-28	Tue 07-Nov-28	38	1494
9
57
58	Presidential Election Day	Not Observed	US	Tuesday after first Monday of November	Public	Tue Nov 5, 2024
59						29	1492
60						29	1492
61
1e

Cell Formulas
Range	Formula
F6:F8	F6	=XLOOKUP(E6,$J$1:$J$6,$J$1:$J$6,,1)
G6:G8	G6	=XLOOKUP(E6,$J$1:$J$6,$J$1:$J$6,,1)-E6
H6:H8	H6	=XLOOKUP(E6+G6+1,$J$1:$J$6,$J$1:$J$6,,1)-E6
J1:J6	J1	=WORKDAY.INTL(DATE(I1,11,0),1,"1011111")
I59	I59	=LET(a,TimeSheet!$E$22,p,$J$1:$J$6,XLOOKUP(a,p,p,,1)-a)
J59	J59	=LET(a,TimeSheet!$E$22+I59+1,p,$J$1:$J$6,XLOOKUP(a,p,p,,1)-TimeSheet!$E$22)
I60	I60	=LET(a,TimeSheet!$E$22,p,{45601;47064;48520;49983;51446;52902},XLOOKUP(a,p,p,,1)-a)
J60	J60	=LET(a,TimeSheet!$E$22+I60+1,p,{45601;47064;48520;49983;51446;52902},XLOOKUP(a,p,p,,1)-TimeSheet!$E$22)

 

[Tetra201]

See if the following formulas work for you:

=WORKDAY.INTL(CEILING(YEAR(Timesheet!$E$22),4)&"-10-31",1,"0111111")+1-IF(MOD(YEAR(Timesheet!$E$22),4),Timesheet!$E$22,0)
=WORKDAY.INTL(CEILING(YEAR(Timesheet!$E$22)-1,4)+1&"-01-19",1,11)-IF(MOD(YEAR(Timesheet!$E$22),4)=1,0,Timesheet!$E$22)

Note: Custom-format the cells as [<2000]#;m/d/yyyy

 

[Sh8dyDan]

See if the following formulas work for you:

=WORKDAY.INTL(CEILING(YEAR(Timesheet!$E$22),4)&"-10-31",1,"0111111")+1-IF(MOD(YEAR(Timesheet!$E$22),4),Timesheet!$E$22,0)
=WORKDAY.INTL(CEILING(YEAR(Timesheet!$E$22)-1,4)+1&"-01-19",1,11)-IF(MOD(YEAR(Timesheet!$E$22),4)=1,0,Timesheet!$E$22)

Note: Custom-format the cells as [<2000]#;m/d/yyyy

The first formula is the one that works.

I had only just recently worked out a brute force option with IF Statements.
But your way is more elegant.

Thanks for the custom formatting tip.

Thank you, Thank you, Thank you!

Dan N.

=WORKDAY.INTL(CEILING(YEAR(Timesheet!$E$22),4)&"-10-31",1,"0111111")+1-IF(MOD(YEAR(Timesheet!$E$22),4),Timesheet!$E$22,0)

Note: Custom-format the cells as [<2000]#;m/d/yyyy