Hours Between Two Times

[rnunley]

Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?

Example:

Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm

Result = 24 hrs

Thanks

RN

 

[OssieMac]

Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

=((A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)))*24

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.

Regards,

OssieMac

 

[OssieMac]

One other thing.

If you want to exclude holidays then you will need to look up the
NETWORKDAYS function in help and create the list of holidays and add the
holidays option into the formula.

Regards,

OssieMac

 

[JStafford]

Hi OssieMac,

I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:

=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

thanks
JS

Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

=((A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)))*24

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.

Regards,

OssieMac

Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?

Example:

Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm

Result = 24 hrs

Thanks

RN

 

[Pete_UK]

Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).

Hope this helps.

Pete

Hi OssieMac,

I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:

=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

thanks
JS

Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.


"rnunley" wrote:

- Show quoted text -

 

[JStafford]

Pete -

Thanks for the suggestion. I checked it and when I format to general I do
get something like below (actually got 39395.70833), so that isn't the
problem.

thx
Jon

Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).

Hope this helps.

Pete

Hi OssieMac,

I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:

=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

thanks
JS

Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.


"rnunley" wrote:

Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?

Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm

Result = 24 hrs

RN- Hide quoted text -

- Show quoted text -

 

[OssieMac]

Also check that G5 is finish date and is greater than F5 which is start date.
That is the finish date/time must be greater than the start date/time because
you cannot have negative date/time.

I copied your formula to a worksheet and inserted a finish date in G5 and
start date in F5 and it works perfectly.

--
Regards,

OssieMac

Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).

Hope this helps.

Pete

Hi OssieMac,

I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:

=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

thanks
JS

Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.


"rnunley" wrote:

Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?

Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm

Result = 24 hrs

RN- Hide quoted text -

- Show quoted text -

 

[JStafford]

Yes, G% was 11/13 and F5 was 11/9. I don't want to take too much of your
time, but could I have the G5 and F5 formatted incorrectly? I formatted them
as:
dddd m/d/yyyy h:mm AM/PM

I'm just at a loss - this should be simple and I'm spending hours trying to
figure it out! Thanks for the input.

Jon

Also check that G5 is finish date and is greater than F5 which is start date.
That is the finish date/time must be greater than the start date/time because
you cannot have negative date/time.

I copied your formula to a worksheet and inserted a finish date in G5 and
start date in F5 and it works perfectly.

--
Regards,

OssieMac

Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).

Hope this helps.

Pete

Hi OssieMac,

I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:

=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

thanks
JS



:
Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

=((A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)))*24

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.

Regards,

OssieMac

:

Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?

Example:

Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm

Result = 24 hrs

Thanks

RN- Hide quoted text -

- Show quoted text -

 

[OssieMac]

Hi Jon,

My test results:-

Cell F5: Friday 11/9/2007 11:00 AM
Cell G5 Tuesday 11/13/2007 1:15 PM

Formula in cell H5 (can be in any cell):-
=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

Returned answer in cell formatted as [h]:mm :-
50:15

Answer looks correct to me.


#VALUE error is more like the values are not valid dates.

--
Regards,

OssieMac

Yes, G% was 11/13 and F5 was 11/9. I don't want to take too much of your
time, but could I have the G5 and F5 formatted incorrectly? I formatted them
as:
dddd m/d/yyyy h:mm AM/PM

I'm just at a loss - this should be simple and I'm spending hours trying to
figure it out! Thanks for the input.

Jon

Also check that G5 is finish date and is greater than F5 which is start date.
That is the finish date/time must be greater than the start date/time because
you cannot have negative date/time.

I copied your formula to a worksheet and inserted a finish date in G5 and
start date in F5 and it works perfectly.

--
Regards,

OssieMac

Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).

Hope this helps.

Pete


On Nov 26, 11:00 pm, JStafford <[email protected]>
wrote:
Hi OssieMac,

I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:

=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

thanks
JS



:
Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

=((A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)))*24

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.

Regards,

OssieMac

:

Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?

Example:

Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm

Result = 24 hrs

Thanks

RN- Hide quoted text -

- Show quoted text -

 

[OssieMac]

Hi Jon,

This could be your problem. You need the Analysis Tool Pak Addin for
NETWORKDAYS function.

Tools->Options->Add-ins and check the box. If not there, lookup in help
about loading addins.
--
Regards,

OssieMac

Hi Jon,

My test results:-

Cell F5: Friday 11/9/2007 11:00 AM
Cell G5 Tuesday 11/13/2007 1:15 PM

Formula in cell H5 (can be in any cell):-
=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

Returned answer in cell formatted as [h]:mm :-
50:15

Answer looks correct to me.


#VALUE error is more like the values are not valid dates.

--
Regards,

OssieMac

Yes, G% was 11/13 and F5 was 11/9. I don't want to take too much of your
time, but could I have the G5 and F5 formatted incorrectly? I formatted them
as:
dddd m/d/yyyy h:mm AM/PM

I'm just at a loss - this should be simple and I'm spending hours trying to
figure it out! Thanks for the input.

Jon

Also check that G5 is finish date and is greater than F5 which is start date.
That is the finish date/time must be greater than the start date/time because
you cannot have negative date/time.

I copied your formula to a worksheet and inserted a finish date in G5 and
start date in F5 and it works perfectly.

--
Regards,

OssieMac


:

Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).

Hope this helps.

Pete


On Nov 26, 11:00 pm, JStafford <[email protected]>
wrote:
Hi OssieMac,

I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:

=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

thanks
JS



:
Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

=((A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)))*24

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.

Regards,

OssieMac

:

Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?

Example:

Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm

Result = 24 hrs

Thanks

RN- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

I would have thought that would produce the #NAME error, rather than
#VALUE.

Pete

Hi Jon,

This could be your problem. You need the Analysis Tool Pak Addin for
NETWORKDAYS function.

Tools->Options->Add-ins and check the box. If not there, lookup in help
about loading addins.
--
Regards,

OssieMac

Hi Jon,

My test results:-

Cell F5: Friday 11/9/2007 11:00 AM
Cell G5 Tuesday 11/13/2007 1:15 PM

Formula in cell H5 (can be in any cell):-
=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

Returned answer in cell formatted as [h]:mm :-
50:15

Answer looks correct to me.

#VALUE error is more like the values are not valid dates.

OssieMac

Yes, G% was 11/13 and F5 was 11/9. I don't want to take too much of your
time, but could I have the G5 and F5 formatted incorrectly? I formatted them
as:
dddd m/d/yyyy h:mm AM/PM
I'm just at a loss - this should be simple and I'm spending hours trying to
figure it out! Thanks for the input.
Jon
:
Also check that G5 is finish date and is greater than F5 which is start date.
That is the finish date/time must be greater than the start date/time because
you cannot have negative date/time.
I copied your formula to a worksheet and inserted a finish date in G5 and
start date in F5 and it works perfectly.
--
Regards,
OssieMac
:
Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).
Hope this helps.
Pete
On Nov 26, 11:00 pm, JStafford <[email protected]>
wrote:
Hi OssieMac,
I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:
=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))
thanks
JS
:
Hi RN,
Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.
Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-
=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))
Example 2:-
Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-
=((A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)))*24
Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.
Regards,
OssieMac
:
Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?
Example:
Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm
Result = 24 hrs
Thanks
RN- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[OssieMac]

Hi Pete,

I unloaded the Analysis Toolpak addin and under test I get #VALUE. Selecting
Show Calculation steps on the error box adjacent to the cell confirms it.
--
Regards,

OssieMac

I would have thought that would produce the #NAME error, rather than
#VALUE.

Pete

Hi Jon,

This could be your problem. You need the Analysis Tool Pak Addin for
NETWORKDAYS function.

Tools->Options->Add-ins and check the box. If not there, lookup in help
about loading addins.
--
Regards,

OssieMac

Hi Jon,

My test results:-

Cell F5: Friday 11/9/2007 11:00 AM
Cell G5 Tuesday 11/13/2007 1:15 PM

Formula in cell H5 (can be in any cell):-
=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))

Returned answer in cell formatted as [h]:mm :-
50:15

Answer looks correct to me.

#VALUE error is more like the values are not valid dates.

"JStafford" wrote:

Yes, G% was 11/13 and F5 was 11/9. I don't want to take too much of your
time, but could I have the G5 and F5 formatted incorrectly? I formatted them
as:
dddd m/d/yyyy h:mm AM/PM

I'm just at a loss - this should be simple and I'm spending hours trying to
figure it out! Thanks for the input.

"OssieMac" wrote:

Also check that G5 is finish date and is greater than F5 which is start date.
That is the finish date/time must be greater than the start date/time because
you cannot have negative date/time.

I copied your formula to a worksheet and inserted a finish date in G5 and
start date in F5 and it works perfectly.

"Pete_UK" wrote:

Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).

Hope this helps.

On Nov 26, 11:00 pm, JStafford <[email protected]>
wrote:
Hi OssieMac,

I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:


:
Hi RN,

Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.

Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-

=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))

Example 2:-

Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-

Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.


"rnunley" wrote:

Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?

Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm

Result = 24 hrs

RN- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

Ah! Right! Something to remember for the future. Thanks for feeding
back.

Pete

Hi Pete,

I unloaded the Analysis Toolpak addin and under test I get #VALUE. Selecting
Show Calculation steps on the error box adjacent to the cell confirms it.
--
Regards,

OssieMac

I would have thought that would produce the #NAME error, rather than
#VALUE.

Pete

Hi Jon,
This could be your problem. You need the Analysis Tool Pak Addin for
NETWORKDAYS function.
Tools->Options->Add-ins and check the box. If not there, lookup in help
about loading addins.
--
Regards,
OssieMac
:
Hi Jon,
My test results:-
Cell F5: Friday 11/9/2007 11:00 AM
Cell G5 Tuesday 11/13/2007 1:15 PM
Formula in cell H5 (can be in any cell):-
=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))
Returned answer in cell formatted as [h]:mm :-
50:15
Answer looks correct to me.
#VALUE error is more like the values are not valid dates.
--
Regards,
OssieMac
:
Yes, G% was 11/13 and F5 was 11/9. I don't want to take too much of your
time, but could I have the G5 and F5 formatted incorrectly? I formatted them
as:
dddd m/d/yyyy h:mm AM/PM
I'm just at a loss - this should be simple and I'm spending hours trying to
figure it out! Thanks for the input.
Jon
:
Also check that G5 is finish date and is greater than F5 which is start date.
That is the finish date/time must be greater than the start date/time because
you cannot have negative date/time.
I copied your formula to a worksheet and inserted a finish date in G5 and
start date in F5 and it works perfectly.
--
Regards,
OssieMac
:
Are you sure that F5 and G5 contain real dates, and not just text
values that look like dates? Try changing the format of both cells to
general, and see if there is any change - a real date/time will look
something like 39412.97708 (today's date and time).
Hope this helps.
Pete
On Nov 26, 11:00 pm, JStafford <[email protected]>
wrote:
Hi OssieMac,
I pasted your formula where start date/time was in F5 and stop date/time was
in G5 and received an VALUE error message. Can you help me understand what
happened? Exact formula:
=(G5-F5)-(ROUND(G5-F5,0)+1-NETWORKDAYS(F5,G5))
thanks
JS
:
Hi RN,
Assuming that your start date/time is in cell A2 and Stop date/time is in
cell A3. I'll give you 2 nested formulas. One if you have the cell formatted
for hrs and mins and one if you want the cell formatted as a standard number.
Example 1:-
Custom format the cell where you want the formula to [h] if you only want to
see hours or [h]:mm if you want to see hours and minutes (The square brackets
around the h force it to display times greater than 24 hours correctly). Then
copy the following formula to the cell:-
=(A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3))
Example 2:-
Format the cell where you want the formula to a number (with or without
decimal places). Then copy the following formula to the cell:-
=((A3-A2)-(ROUND(A3-A2,0)+1-NETWORKDAYS(A2,A3)))*24
Hope it works as you want and if it does then if you posted via the MS
Communities website then don't forget to answer 'yes' to the question was
this helpful.
Regards,
OssieMac
:
Is there a way to caluculate the hours between two dates and times while
excluding weekends and holidys?
Example:
Start - 8/24/2007 12:00pm
Stop - 8/27/2007 12:00pm
Result = 24 hrs
Thanks
RN- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -