Date formula that will return proper pay day

[JJ in LA]

I need to set up a schedule that would list bi-monthly paydays based on the
15th and last workday of the month. When a pay day falls on a weekend or
holiday it must change to the last workday prior to the 15th or the last
workday of the month.

Is there a simple formula I can use to list what those paydays would be? My
formula goes on and on and I know there must be an easier way.

 

[Ron Rosenfeld]

I need to set up a schedule that would list bi-monthly paydays based on the
15th and last workday of the month. When a pay day falls on a weekend or
holiday it must change to the last workday prior to the 15th or the last
workday of the month.

Is there a simple formula I can use to list what those paydays would be? My
formula goes on and on and I know there must be an easier way.

If I understand what you want:

With some date in the month of interest in A1:

The workday that is either the 15th of the month, or the first workday prior to
that date:

=WORKDAY(A1-DAY(A1)+16,-1,[holidays])

The last workday of the month:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1,[holidays])

[holidays] represents a range of cells where the holiday dates are stored.

If the function returns a #NAME error, and you are using a version prior to
Excel 2007, see HELP for the WORKDAY function for instructions as to how to
resolve the problem.
--ron

 

[Matt]

JJ -

1.) Enter the dates you want to analyze in column A (ie A1 is 1/15, A2 is
1/31)
2.) Enter the following in cell B1:

=IF(WEEKDAY(A2) = 1, A2 - 2, IF(WEEKDAY(A2) = 7, A2 - 1,A2))

3.) Make sure your cells are formatted as DATE
4.) Copy and paste the formula down

In this example, Jan 29 will display in cell B2 because Jan 31 falls on a
Sunday

---

The formula works as such: Analyzes the date and returns a serial
corresponding to the Day of the Week. 1 = Sunday, 2 = Monday, etc. If the
serial is 1, then subtract 2 days from the date to get to Friday, if the
serial is 7 (for Saturday) then subtract 1 day from the date to get to
Friday, otherwise leave the date unchanged.


:

 

[Ron Rosenfeld]

JJ -

1.) Enter the dates you want to analyze in column A (ie A1 is 1/15, A2 is
1/31)
2.) Enter the following in cell B1:

=IF(WEEKDAY(A2) = 1, A2 - 2, IF(WEEKDAY(A2) = 7, A2 - 1,A2))

3.) Make sure your cells are formatted as DATE
4.) Copy and paste the formula down

In this example, Jan 29 will display in cell B2 because Jan 31 falls on a
Sunday

---

The formula works as such: Analyzes the date and returns a serial
corresponding to the Day of the Week. 1 = Sunday, 2 = Monday, etc. If the
serial is 1, then subtract 2 days from the date to get to Friday, if the
serial is 7 (for Saturday) then subtract 1 day from the date to get to
Friday, otherwise leave the date unchanged.

Note that your method does not correct for holidays.
--ron

 

[Matt]

Thanks for pointing that out, Ron.

Note that your method does not correct for holidays.
--ron
.