The next payday

[paulikk]

I need a formula to find the next payday based on the 15th of the month, starting from today. When the payday falls on a weekend or holiday it must change to the last workday prior to the 15th of the month.

A1=today date;
A2,A3...A10=holidays date

thank you!

[Jonmo1]

Try

=WORKDAY($A$1-DAY($A$1)+16,-1,$A$2:$A$10)

[joris moerings]

Hi,

This formula is date independent, it just requires the current month taken from TODAY() function but can be replaced with any datevalue;
=IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15),2)>=6;WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15),-1;holidays),WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15),0,holidays))

[kvsrinivasamurthy]

Formula

Please Login or Register  to view this content.

[bebo021999]

=WORKDAY($A$1-DAY($A$1)+16,-1,$A$2:$A$10)

Do not work with day>15

My formula, same result with kvsrinivasamurthy's, but other approach:

=INDEX(WORKDAY(EOMONTH($A$1,{0,-1})+16,-1,$A$2:$A$10),MATCH($A$1,WORKDAY(EOMONTH($A$1,{0,-1})+16,-1,$A$2:$A$10)-10^-10,-1))

[Jonmo1]

Ah yes, the key word is 'Next' Payday.

The first 2 formulas suggested find just the 15th of the current month.
But if today's date is already past the 15th, then we need the 15th of the next month.

So kvsrin's formula is the only one that accounts for that.
But not quite.
It's testing if the DAY(A1) is < 15
This would fail If the 15th of current month happens to be a weekend or Holiday, because the payday would then be the previous working day from 15th.

Here's an ammendment to mine that would work for that as well.
=WORKDAY(EOMONTH($A$1,IF(WORKDAY(EOMONTH($A$1,-1)+16,-1,$A$2:$A$10)<$A$1,0,-1))+16,-1,$A$2:$A$10)

[bebo021999]

Hi Jonmo
If A1=15th-Jan, your result is on same day, next pay day should be 15-Feb. That why in my formula, I have to subtract 10^-10 to each date found.

[bebo021999]

Let's test 3 formulas with:
A1=12-May
A2=13-May
kvsrin's and Jonmo say: 12-May
but mine say: 15-Jun

[Jonmo1]

Hi Jonmo
If A1=15th-Jan, your result is on same day, next pay day should be 15-Feb.

That's ambiguous, it's up to the OP to decide if the current date should be returned or not.
If it were up to me, I'd say if today was a payday, then it should return today.

Easy enough to adjust in my formula, just change the <$A$1 to <=$A$1

FYI, I hadn't seen your post yet at the time I made my last post.

[paulikk]

Thank you very much to everyone. All the formulas are working good.
I'd like to put another holidays in formulas. could you help me?

i mean:
A1=today date
A2,A3...A10=holidays
B2,B3...B10=another holidays

thanks a lot!

[Jonmo1]

You're welcome.

I'd like to put another holidays in formulas. could you help me?

A2,A3...A10=holidays
B2,B3...B10=another holidays

Sure, just change $A$2:$A$10 to $A$2:$B$10

[paulikk]

Sorry i make a mistake. The columns of holidays aren't side by side. Let's sey
A1=today date
A2,A3...A10=holidays
B2,B3...B10=something else
C2,C3...C10=holdays

thanks!

[Jonmo1]

Put all the holidays in 1 contiguous range.

Or are you wanting to make in conditional, like
If Condition1 is True, then use A2:A10
But if Condition2 is True, then use C2:C10

?

[paulikk]

is not a solution for me. i don't have space to do that.
i need to combine the holidays columns in WORKDAY formula.

[Jonmo1]

i don't have space to do that.

I find that difficult to believe.

XL2013 has 1,048,576 Rows and 16,384 Columns.
That's 17,179,869,184 available cells in a single sheet.
You can't find 20 available consecutive cells to use?

I understand you may not want to change the current layout.
But you can't find 20 contiguous cells somewhere outside of your currently used area to use as 'Helper' cells ?
They don't even have to be on the same sheet.

Say in G2 put =A2 and fill down
Then in G11 put = C2 and fill down.
Now all the dates from A2:A10 and C2:C10 are contained in G2:G19

Use that in the workday function.

[paulikk]

you're right. it is a solution. i can do like this. thanks a lot.

[Jonmo1]

You're welcome.

[Jonmo1]

it is a solution.

Just an FYI or Opinion here.

You seem to be disapointed that you have to use helper cells to do this.

You really shouldn't think of it that way.
Helper cells are NOT a bad thing, that's why we call them 'Helper', because they Help.
Like I said, XL gives you over 17 Billion cells to work with (in just 1 sheet). Might as well use a few of them.

It's not a performance hit, it's only 18 cells. With just direct =cell references, no additional calcluations are being done.
Particularly since you may have more than 1 formula that needs to reference these holiday dates.

You can make it a little 'Neater' by putting them in another sheet, and hiding that sheet, and including the sheet reference in your workday function.
You can even make it a Named Range
Highlight those cells and on the Formulas Tab, click Name Manager - New
Give it a name, say Holidays

Then you can use it in your workday function by just writing the named range
Worday(A1,1,Holidays)


Hope that's helpful.

[kvsrinivasamurthy]

Try this formula.
No helper cells, no additional work
A2:A10 holidays list
C2:C12 some other holiday list

Please Login or Register  to view this content.