Calculating pay days

[CamaroLT]

I'm attempting to figure out a formula that calculates the next pay date based on the criteria that
- The pay day is based on the 1st and 15th of the month
- If the 1st or the 15th happen on a weekend, pay is processed the Friday before.

I've found a whole lot of formulas that calculate with a modulus against what week number it currently is, but, I'm paid twice a month based on above criteria, not an even number of days between.

Any ideas?

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[Andrzejek]

I assume you want to do that in Excel, right?
Do you want to have all pay days (dates) in a given year?
Do you have a date and you want to know when you will get paid for working in this date?
So the question is - what data do you have available?

Is the solution in VBA acceptable?

If so, in the standard Module in Excel:

Option Explicit

Public Function PayDay(ByRef dte As Date) As Date[green]
'- The pay day is based on the 1st and 15th of the month
'- If the 1st or the 15th happen on a weekend, pay is processed the Friday before.[/green]

Dim datOut As Date

Select Case Day(dte)
    Case 16 To 31
        datOut = DateSerial(Year(dte), Month(dte) + 1, 1)
    Case Is < 16
        datOut = DateSerial(Year(dte), Month(dte), 15)
End Select

Select Case Weekday(datOut)
    Case vbSunday
        datOut = DateAdd("d", -2, datOut)
    Case vbSaturday
        datOut = DateAdd("d", -1, datOut)
End Select

PayDay = datOut

End Function

In Excel, format columns A and B as Dates:
[pre]
A B
1 3/30/2017 [blue] =PayDay(A1)[/blue]
2 3/31/2017
3 4/1/2017
4 4/2/2017
5 ....
[/pre]
Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[CamaroLT]

Step in the right direction. But I found an issue (I'm sure just math, which I can figure out) but I I set refrence date to Apr 16, a day beyond Apr 15, and the 15th happens on the weekend, I'm still showing Apr 14. The fix I'll put in is just add a couple days if current date (A1) >= Paydat(a1) or something of the sort.

Thanks, this puts me on the right track.

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[SkipVought]

Hi,

This is the Office forum, where spreadsheets solutions are expected for Excel.

So, if you have these dates with my results...
[pre]
4/1/2017 3/31/2017
4/15/2017 4/14/2017
5/1/2017 5/1/2017
[/pre]

And my formula...
[tt]
=if(WEEKDAY(E3)=1,-2,if(WEEKDAY(E3)=7,-1,0))+E3
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

I think Andy's code is correct. When I test April 16th, it returns May 1st. For April 16th, it doesn't make any difference what day of the week April 15th falls on since the pay date would be based on the first of the next month which is May. May 1st is a Monday. If May 1st was a Saturday then I assume the pay date for April 16th would be April 30th. June 16th's pay date is June 30th since July 1st is a Saturday.


Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

>When I test April 16th, it returns May 1st

Whether that is correct depends on if you are paid in arrears or in advance, surely? The OP needs to let us know ...

 

[Andrzejek]

I would take Skip's suggestion and his formula since this is not a VBA forum (forum707)

We still have a very vague idea of what CamaroLT has, in which format, and my original questions are not answered. That's why there are a lot of assumptions here. :-(

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[zelgar]

Additionally, depending on the version of Excel, the Weekday function has several options that may can make things easier:

=IF(WEEKDAY(E3,16)<3,E3-WEEKDAY(E3,16),E3)

The Option of 16 for Weekday results in a 1 or 2 for the weekend, making only 1 logic statement and calculating the date easier.

 

[CamaroLT]

@Andrzejek

The answers to your questions are as follows;

Q>I assume you want to do that in Excel, right?
A>It'd be preferred, otherwise, I'd resort to going into an actual programming language, and design my own app. ;}

Q>Do you want to have all pay days (dates) in a given year?
A>No. Just a date specified in a cell. Plan is to use this in different places.

Q>Do you have a date and you want to know when you will get paid for working in this date?
A>Yes.

Q>So the question is - what data do you have available?
A>A date specified to base the next paydate on

Q>Is the solution in VBA acceptable?
A>It is, and in use.

@All
I've tried all Excel formulas here, and I'm still getting Apr 14, 2017 as a pay day when the original date being calculated against is April 15, 2017. I THINK maybe the problem has something to do with UTC time conversion or some jazz like that, I don't know. Either way, something I can fix on my end.

Also, my requirements were wrong. Payday happens on the Friday before or on the 15th and last day of the month. Easy fix though, now that I've got stuff to work with.

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[strongm]

Your Excel code now pretty much matches the VB I'd written for this ...

[blue]Public Function CheckDate(testdate As Date) As Date
    CheckDate = IIf(Weekday(testdate, vbSaturday) < 3, testdate - Weekday(testdate, vbSaturday), testdate)
End Function[/blue]