Excel Calculate business date

[clevelandjwb]

I am trying to detremine a formula (VBA function would be acceptable) to calculate the actual pay date given the nominal pay date. As an example: the nominal paydate for the first payroll in January is 1/15/06. I would like the function to return 1/13/06 which is the last working day prior to the nominal pay date. It would be nice if it could use the holiday function that is present in several of the excel.

I converted this worksheet from Quattro and there used the @busday function. This function allows you to supply a date and based on that date either go forward or backward to the next/previous business day. Quattro help defines the operation of the function as:

@BUSDAY returns Date if it is a valid business day. If Date falls on a Saturday (and Saturday is set to 0 or omitted), Sunday (and Sunday is set to 0 or omitted), or holiday, @BUSDAY returns the date of the closest valid business day in the direction specified by Direction.

The Excel Weekday function just doesn't seem to have this flexability.

John

 

[SkipVought]

Hi,

You don't need VBA. It can be done right on a sheet.

Play around with...
[tt]
=INT((YourDate/7)+x)*7+y
[/tt]
x & y can be references to 2 different cells.
x & y can be negative or positive.
x & y adjust where the week break occurs.


Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]

 

[GlennUK]

Hi John,

if you have the Analysis Toolpak added in you can use the WORKDAY function.

So, =WORKDAY(DATEVALUE("15/JAN/06"),-1) will return 13th Jan. ( you can replace the DATEVALUE(...) bit with a reference to the required date.

If you have a defined name covering a list of your holidays, that can be included:
=WORKDAY(DATEVALUE("15/JAN/06"),-1,ListOfHolidays)



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.