I get paid on the business day of the fifteenth and the last day of the month. However, some months those days fall on weekends, and we are paid the closest business day prior (i.e. 6/30/18 falls on a Saturday, checks get cut Friday 6/29/18).
Currently I'm using the following formula to calculate per diem based on the difference between today and the next pay day:
=G2/if(day(today())<16,15-day(today()),EOMONTH(today(),0)-today())
However, it targets 6/30 from the above example, and I would like it to use 6/29.
Is there a formula to conditionally calculate the pay date based on business days prior?
Best Answer
This solution solves for calendar days. In order to calculate per diem, you need to know the sum of funds and the days they would be distributed across (e.g. $100 for 10 days is $10/day).
Where the sum for the remainder of the pay period is in G2, the divisor is the difference in calendar days between today and either the 15th or last day of the month (or the previous business day if a non-business day), based on the day of the month.
For a concrete example, today is the 6/21, the last day of the month is 6/30. 6/30 is a Saturday, so the previous business day is 6/29. There are eight days (including today) between now and the pay date, so the formula would be G2/8.
This formula solves for per diem based on that pattern, and works for today() being any day in the month:
This formula does not account for holidays