Calculating working hours

http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"John" <[email protected]> wrote in message
news:[email protected]...
>I want to calculate the number of working hours between two dates.
>
> As an example: I receive a fault call and log the date and time. I also
> record the date and time when it is resolved. I then need to calculate the
> number of hours it took to resolve the call taking into account the fact
> that
> I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> between
> the two points easily enough but can't find a way to account for the
> non-working hours over night and the weekends.
>
> I am using Excel 2000 & XP.
>
> Any help/ideas gratefully received.
>
> Thanks.

Hi Niek

I had already tried this one - can't make it work.

Kind regards

John


"Niek Otten" wrote:

> http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
> >I want to calculate the number of working hours between two dates.
> >
> > As an example: I receive a fault call and log the date and time. I also
> > record the date and time when it is resolved. I then need to calculate the
> > number of hours it took to resolve the call taking into account the fact
> > that
> > I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> > between
> > the two points easily enough but can't find a way to account for the
> > non-working hours over night and the weekends.
> >
> > I am using Excel 2000 & XP.
> >
> > Any help/ideas gratefully received.
> >
> > Thanks.
>
>
>

Hi!

Enter the date/time in a single cell:

A1 = 10/1/2004 2:25 PM

A2 = 10/15/2004 5:02 PM

=IF(NETWORKDAYS(A1,A1)=1,17/24-MOD(A1,1),0)
+IF(NETWORKDAYS(A2,A2)=1,MOD(A2,1)-9/24,0)+NETWORKDAYS(A1+1,A2-1)*8/24

Format the cell as [h]:mm

Also, if you want to account for holidays, that is, exclude holidays because
those days are not worked, then you need to make a list of those holidays
and include a reference to that list as a 3rd argument to the Networkdays
function.

Biff

"John" <[email protected]> wrote in message
news:[email protected]...
>I want to calculate the number of working hours between two dates.
>
> As an example: I receive a fault call and log the date and time. I also
> record the date and time when it is resolved. I then need to calculate the
> number of hours it took to resolve the call taking into account the fact
> that
> I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> between
> the two points easily enough but can't find a way to account for the
> non-working hours over night and the weekends.
>
> I am using Excel 2000 & XP.
>
> Any help/ideas gratefully received.
>
> Thanks.

http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"John" <[email protected]> wrote in message
news:[email protected]...
>I want to calculate the number of working hours between two dates.
>
> As an example: I receive a fault call and log the date and time. I also
> record the date and time when it is resolved. I then need to calculate the
> number of hours it took to resolve the call taking into account the fact
> that
> I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> between
> the two points easily enough but can't find a way to account for the
> non-working hours over night and the weekends.
>
> I am using Excel 2000 & XP.
>
> Any help/ideas gratefully received.
>
> Thanks.

Hi Niek

I had already tried this one - can't make it work.

Kind regards

John


"Niek Otten" wrote:

> http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
> >I want to calculate the number of working hours between two dates.
> >
> > As an example: I receive a fault call and log the date and time. I also
> > record the date and time when it is resolved. I then need to calculate the
> > number of hours it took to resolve the call taking into account the fact
> > that
> > I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> > between
> > the two points easily enough but can't find a way to account for the
> > non-working hours over night and the weekends.
> >
> > I am using Excel 2000 & XP.
> >
> > Any help/ideas gratefully received.
> >
> > Thanks.
>
>
>

Hi!

Enter the date/time in a single cell:

A1 = 10/1/2004 2:25 PM

A2 = 10/15/2004 5:02 PM

=IF(NETWORKDAYS(A1,A1)=1,17/24-MOD(A1,1),0)
+IF(NETWORKDAYS(A2,A2)=1,MOD(A2,1)-9/24,0)+NETWORKDAYS(A1+1,A2-1)*8/24

Format the cell as [h]:mm

Also, if you want to account for holidays, that is, exclude holidays because
those days are not worked, then you need to make a list of those holidays
and include a reference to that list as a 3rd argument to the Networkdays
function.

Biff

"John" <[email protected]> wrote in message
news:[email protected]...
>I want to calculate the number of working hours between two dates.
>
> As an example: I receive a fault call and log the date and time. I also
> record the date and time when it is resolved. I then need to calculate the
> number of hours it took to resolve the call taking into account the fact
> that
> I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> between
> the two points easily enough but can't find a way to account for the
> non-working hours over night and the weekends.
>
> I am using Excel 2000 & XP.
>
> Any help/ideas gratefully received.
>
> Thanks.

I want to calculate the number of working hours between two dates.

As an example: I receive a fault call and log the date and time. I also
record the date and time when it is resolved. I then need to calculate the
number of hours it took to resolve the call taking into account the fact that
I only work Monday - Friday & 9am to 5pm. I can calculate the hours between
the two points easily enough but can't find a way to account for the
non-working hours over night and the weekends.

I am using Excel 2000 & XP.

Any help/ideas gratefully received.

Thanks.

http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"John" <[email protected]> wrote in message
news:[email protected]...
>I want to calculate the number of working hours between two dates.
>
> As an example: I receive a fault call and log the date and time. I also
> record the date and time when it is resolved. I then need to calculate the
> number of hours it took to resolve the call taking into account the fact
> that
> I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> between
> the two points easily enough but can't find a way to account for the
> non-working hours over night and the weekends.
>
> I am using Excel 2000 & XP.
>
> Any help/ideas gratefully received.
>
> Thanks.

Hi Niek

I had already tried this one - can't make it work.

Kind regards

John


"Niek Otten" wrote:

> http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
> >I want to calculate the number of working hours between two dates.
> >
> > As an example: I receive a fault call and log the date and time. I also
> > record the date and time when it is resolved. I then need to calculate the
> > number of hours it took to resolve the call taking into account the fact
> > that
> > I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> > between
> > the two points easily enough but can't find a way to account for the
> > non-working hours over night and the weekends.
> >
> > I am using Excel 2000 & XP.
> >
> > Any help/ideas gratefully received.
> >
> > Thanks.
>
>
>

Hi!

Enter the date/time in a single cell:

A1 = 10/1/2004 2:25 PM

A2 = 10/15/2004 5:02 PM

=IF(NETWORKDAYS(A1,A1)=1,17/24-MOD(A1,1),0)
+IF(NETWORKDAYS(A2,A2)=1,MOD(A2,1)-9/24,0)+NETWORKDAYS(A1+1,A2-1)*8/24

Format the cell as [h]:mm

Also, if you want to account for holidays, that is, exclude holidays because
those days are not worked, then you need to make a list of those holidays
and include a reference to that list as a 3rd argument to the Networkdays
function.

Biff

"John" <[email protected]> wrote in message
news:[email protected]...
>I want to calculate the number of working hours between two dates.
>
> As an example: I receive a fault call and log the date and time. I also
> record the date and time when it is resolved. I then need to calculate the
> number of hours it took to resolve the call taking into account the fact
> that
> I only work Monday - Friday & 9am to 5pm. I can calculate the hours
> between
> the two points easily enough but can't find a way to account for the
> non-working hours over night and the weekends.
>
> I am using Excel 2000 & XP.
>
> Any help/ideas gratefully received.
>
> Thanks.