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]

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

=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


My bad, actually both formulas work. I was so focused on the first one that I assumed the formulas you submitted were variations of the same formula.

Thank you,

Dan N.

 

[Tetra201]

Glad to have helped

 

[Peter_SSs]

Thanks for the further example.
I have also now been able to find out a bit more about USA election and inauguration days which I previously knew little about & hence my questions. (Sorry they seem to have annoyed you.)
Not highly tested but I think that this slightly different approach also does what you want. Same custom format as suggested above.

=LET(d,Timesheet!$E$22,y,YEAR(d),s,DATE(CEILING(y,4),11,2),s+7-WEEKDAY(s+4)-d*(MOD(y,4)>0))
=LET(d,Timesheet!$E$22,y,YEAR(d),s,DATE(CEILING(y-1,4)+1,1,20),s+(WEEKDAY(s)=1)-d*(MOD(y,4)<>1))

 

[Sh8dyDan]

Thank you for your follow-up post. Your formulas do return the correct results.

I previously knew little about & hence my questions. (Sorry they seem to have annoyed you.)

No problem. It was 3 AM where I was. I want all MVPs and anyone who donates their time answering questions to know you are appreciated. I am a novice, a hobbyist at best, but I also find building useful things in Excel changing and rewarding like you. Please accept my apologies.

Dan N.

 

[Peter_SSs]

Cheers, no problem. We all have moments like that.