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?