Date manipulation

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.

--
Rick

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...)


In article <[email protected]>,
"Rick A" <[email protected]> wrote:

> 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,

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,

--
Rick


"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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...)
>
>
> In article <[email protected]>,
> "Rick A" <[email protected]> wrote:
>
>> 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.

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

In article <#[email protected]>,
"Rick A" <[email protected]> wrote:

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

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

--
Rick Allison
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> You could certainly write your own routine, but I don't know any
> built-in way.
>
> In article <#[email protected]>,
> "Rick A" <[email protected]> wrote:
>
>> Is there any other option you know of that does not use the Analysis
>> Toolpak?

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


"Rick A" <[email protected]> wrote in message
news:[email protected]...
>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.
>
> --
> Rick
>