Days Until Next Pay Day Each Month (26th) , If 26th is weekend rollback to nearest Friday

Hello,
I have been trying to write a formula that calculates the number of days until the next pay day. If the next payday is a Saturday or Sunday, use the nearest Friday for the end date.

I have all the pieces but cannot stitch them all together and have clearly overcomplicated this and exceeded my comfort level.

Here is what I have been working through

Start with which month I should be referencing
=IF(DAY(TODAY())<26,"This Month","Next Month")

Work Through the This Month Scenario

1. Calculate the days untill the 26th of This Month
=IF(DAY(TODAY())<26,DAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),26),TODAY()),"Next Month")

2. Check if the 26th is a weekend
=IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),26),2)>5,"Weekend, Find Nearest Friday","Weekday, Use the 26th")

3. Find Nearest Friday
=DATE(YEAR(TODAY()),MONTH(TODAY()),26-MOD(WEEKDAY(A1)-6,7))

4. Integrate 2 and 3
=IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),26),2)>5,DATE(YEAR(TODAY()),MONTH(TODAY()),26-MOD(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),26)-6,7)),DATE(YEAR(TODAY()),MONTH(TODAY()),26))

5 Integrate 1 and 4

=IF(DAY(TODAY())<26,DAYS(F(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),26),2)>5,DATE(YEAR(TODAY()),MONTH(TODAY()),26-MOD(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),26)-6,7)),DATE(YEAR(TODAY()),MONTH(TODAY()),26)),TODAY()),"Next Month")

6. Now Move To Next Month Scenario, Add +1 To the Month from 4
=IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()+1),26),2)>5,DATE(YEAR(TODAY()),MONTH(TODAY()+1),26-MOD(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()+1),26)-6,7)),DATE(YEAR(TODAY()),MONTH(TODAY()),26))

7. Integrate 6 and 5

Final Formula is this correct? If it is i am expecting it to fail come december 2022 :-(
=IF(DAY(TODAY())<26,DAYS(IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),26),2)>5,DATE(YEAR(TODAY()),MONTH(TODAY()),26-MOD(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),26))-6,7)),DATE(YEAR(TODAY()),MONTH(TODAY()),26)),TODAY()),DAYS(IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()+1),26),2)>5,DATE(YEAR(TODAY()),MONTH(TODAY()+1),26-MOD(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()+1),26))-6,7)),DATE(YEAR(TODAY()),MONTH(TODAY()+1),26)),TODAY()))

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Try this:

=LET(pd,DATE(YEAR(TODAY()),MONTH(TODAY()),26),IF(DAY(TODAY())<26,pd-LOOKUP(WEEKDAY(pd,2),{0,6,7},{0,1,2}),EDATE(pd,1)-LOOKUP(WEEKDAY(EDATE(pd,1),2),{0,6,7},{0,1,2})))

or this:

=LET(pd,DATE(YEAR(TODAY()),MONTH(TODAY()),26),pdn,EDATE(pd,1),IF(DAY(TODAY())<26,pd-LOOKUP(WEEKDAY(pd,2),{0,6,7},{0,1,2}),pdn-LOOKUP(WEEKDAY(pdn,2),{0,6,7},{0,1,2})))

It's doing what you described in your opening sentence. If you need it explaining, just ask.

If you need more help, attach a workbook.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.