Date manipulation

[Rick A]

I need a boost.

I need to calculate a start date based on an end date and hours and wonder
how to do it.

For example, If the end date is Friday and the number of hours is 8 the
start date needs to be Friday too. If the end date is Friday and the number
of hours is between 8 and 16 the start date needs to be Thursday. And so
on. I need to take into account weekends but I'm going to ignore holidays
for now.

I'm having a hard time figuring out how to subtract hours from a date. I
need to consider each date as being a 8 hour day too.

Your help is appreciated.

 

[JE McGimpsey]

One way:

A1: <end date>
A2: <# hours>
A3: =WORKDAY(A1,-INT((A2-0.0000001)/8))

Workday is an Analysis Toolpak Add-in function (Tools/Options/Add-ins...)

 

[Rick A]

JE,

Thanks. That works.

Forgot to mention, I need to use this routine in VBA. I know how to
reference the Analysis Toolpak, no problem there.

Is there any other option you know of that does not use the Analysis
Toolpak?

Thanks,

 

[JE McGimpsey]

You could certainly write your own routine, but I don't know any
built-in way.

 

[Rick A]

Thanks. I'm not that ambitious. I'll use the existing tool.

 

[Vsn]

Your problem might be solved but if at some stage you want to take it a bit
further check this usefull site:-

http://www.cpearson.com/excel/holidays.htm

Vsn