Formula for days until next payday

**Ady2673** · 07-25-2020, 01:06 PM

I'd like a formula to calculate the number of days until next payday.

I get paid monthly on the 25th, however, if the 25th falls on a weekend or UK bank holiday, I get paid on the Friday.

I did think a table would help.
A Day (starts Monday)
B Date (starts Dec 23, 2019)
C Month (starts Jan-20) (NB I use payday to determine the next period, so Jan-20 runs from Dec 23, 2019 until Jan 23, 2020 etc.)
D Week (starts 1)
E Payday (Yes or No)

Today is Saturday Jul 25, 2020 and I was paid yesterday. My next payday will be Tuesday Aug, 25, 2020.

I then though an Index and Match function would work, but not sure how to reference the match without writing a formula and changing it every month anyway.

I've seen this,
=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>25),25)-TODAY()
but the formula just returns an error

Any help would be much appreciated.

**forestview** · 07-25-2020, 01:17 PM

Try

=DATEDIF(Date1,Date2,"D") Date 1 being the earlier date

**Ady2673** · 07-25-2020, 01:29 PM

Thanks for your prompt reply. I can enter Date1 as Jul 24, 2020 in, say, C1 and Date2 as Aug 25, 2020 in C2 and write this formula but it would need to be re-written every month and I'm trying to avoid that.
=DATEDIF(C1,C2,"D")

**forestview** · 07-25-2020, 01:34 PM

You can enter

=DATEDIF(TODAY(),C2,"D") Otherwise provide an example sheet so we can see exactly what you require.

**ben_hensel** · 07-25-2020, 01:45 PM

This is an interesting logical challenge. I broke it down into a couple of parts:

In cell A1 is the input date.

Please Login or Register  to view this content.

In cell A2 is the 25th of the month from after today.

Please Login or Register  to view this content.

(Note that you can put TODAY() instead of the "A1" there, I made it a reference so I could manually enter things in A1 for testing).
Note that DATE(2020, 13, 25) is resolved as "Jan 25, 2020" so that will handle rolling over into the next year for us automatically.

In cell A3 we have a weekend handler

Please Login or Register  to view this content.

This will resolve as 1 if A2 is a Saturday or a 2 if A2 is a Sunday.

In cell A4 we have a holiday handler

Please Login or Register  to view this content.

This will return 1 in two cases:
1) A2 itself is a holiday.
2) If A2 - A3 (eg, the presumptive next payday) is a holiday. (To handle a case where the 25th is on the weekend but the 23rd or 24th, for whatever reason, is a bank holiday and we need to pull payday forward to Thursday).
Note this is assuming a manually entered range of Bank Holidays in cells D1:D10, with each holiday given it's own cell. If you already have that somewhere else in your spreadsheet you can point there instead.

In cell A5 is the date of the next payday

Please Login or Register  to view this content.

Note that because of the choice in A2 to check for dates greater than the 25th, this will tell you that "today" is the next payday when A1 =August 25th, 2020.
If you wanted it to instead say that the next payday is September 25th, you'd put a ">=" instead of a ">" in the "DAY()>A1" term in cell A2.

Finally in Cell A6 is The Output, "Days until Next Payday"

Please Login or Register  to view this content.

If you dropped the "A1=TODAY()" cell usage you can just make it "=A5-TODAY()" instead

**Ady2673** · 07-25-2020, 02:18 PM

Ok, So I have created a VLOOKUP on one sheet which gives me the last instance of salary
=LOOKUP(2,1/(C:C="Salary"),A:A)
where column G contains categories and column A contains dates.

This gives me a working Date1 cell reference.

I've attached a basic worksheet to demonstrate what I'm trying to achieve