Caluculate difference between two times
 

I am looking for a formula to calculate the difference between two times in
hours. I also want to exclude the time between 11 PM on Friday night and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

--
BRO

Caluculate difference between two times
 

assuming that neither of your start or end date/times will fall between 11 PM
Friday and 11 PM Sunday you can use this formula

=(NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-MOD(A1+"1:00",1))*24

note: NETWORKDAYS is part of Analysis ToolPak. If you get #NAME! error with
the above then you need to install. Tools addins tick "Analysis ToolPak"

"BRO" wrote:

I am looking for a formula to calculate the difference between two times in
hours. I also want to exclude the time between 11 PM on Friday night and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

--
BRO

Caluculate difference between two times
 

Hi

Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis
Toolpak, then

=NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
If you want to exclude Holidays as well as weekends, then add the 3rd
parameter to Networkdays which can either be cell references holding a
list of holiday dates or a named range holding holiday dates

=NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
where X1:X9 hold a list of holiday dates
--
Regards

Roger Govier


"BRO" wrote in message
...
I am looking for a formula to calculate the difference between two
times in
hours. I also want to exclude the time between 11 PM on Friday night
and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

--
BRO

Caluculate difference between two times
 

The formula work great for most of my data, however on ocassion I do have
start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop
date however will never fall in this time period.

Any ideas for these cases?
--
BRO


"daddylonglegs" wrote:

assuming that neither of your start or end date/times will fall between 11 PM
Friday and 11 PM Sunday you can use this formula

=(NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-MOD(A1+"1:00",1))*24

note: NETWORKDAYS is part of Analysis ToolPak. If you get #NAME! error with
the above then you need to install. Tools addins tick "Analysis ToolPak"

"BRO" wrote:

I am looking for a formula to calculate the difference between two times in
hours. I also want to exclude the time between 11 PM on Friday night and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

--
BRO

Caluculate difference between two times
 

The formula worked great. Any suggestions for when the start date would fall
between 11 PM Friday night and 11 PM Sunday? On ocassion the start date
would fall in this time period but the stop date would not. On these
ocassion the total time would start from 11 PM Sunday night.

Thanks,
--
BRO


"Roger Govier" wrote:

Hi

Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis
Toolpak, then

=NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
If you want to exclude Holidays as well as weekends, then add the 3rd
parameter to Networkdays which can either be cell references holding a
list of holiday dates or a named range holding holiday dates

=NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
where X1:X9 hold a list of holiday dates
--
Regards

Roger Govier


"BRO" wrote in message
...
I am looking for a formula to calculate the difference between two
times in
hours. I also want to exclude the time between 11 PM on Friday night
and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

--
BRO

Caluculate difference between two times
 

Hi

I think the following will work, making use of a "helper cell". I used
A2.
In A2
=IF(OR(AND(WEEKDAY(A1,2)=5,MOD(A1,1)*24=23),
WEEKDAY(A1,2)5),DATE(YEAR(A1),
MONTH(A1),DAY(A1)+7-WEEKDAY(A1,2))+TIME(23,0,0),A1)

Then in cell to have the result
=IF(A1=A2,NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24),(B1-A2)*24)

--
Regards

Roger Govier


"BRO" wrote in message
...
The formula worked great. Any suggestions for when the start date
would fall
between 11 PM Friday night and 11 PM Sunday? On ocassion the start
date
would fall in this time period but the stop date would not. On these
ocassion the total time would start from 11 PM Sunday night.

Thanks,
--
BRO


"Roger Govier" wrote:

Hi

Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis
Toolpak, then

=NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
If you want to exclude Holidays as well as weekends, then add the 3rd
parameter to Networkdays which can either be cell references holding
a
list of holiday dates or a named range holding holiday dates

=NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
where X1:X9 hold a list of holiday dates
--
Regards

Roger Govier


"BRO" wrote in message
...
I am looking for a formula to calculate the difference between two
times in
hours. I also want to exclude the time between 11 PM on Friday
night
and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

--
BRO

Caluculate difference between two times
 

Hello BRO

"however on ocassion I do have
start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop
date however will never fall in this time period"

just amend my suggested formula to:

=NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-NETWORKDAYS(A1+"1:00",A1+"1:00")*MOD(A1+"1:00",1)

"Roger Govier" wrote:

Hi

I think the following will work, making use of a "helper cell". I used
A2.
In A2
=IF(OR(AND(WEEKDAY(A1,2)=5,MOD(A1,1)*24=23),
WEEKDAY(A1,2)5),DATE(YEAR(A1),
MONTH(A1),DAY(A1)+7-WEEKDAY(A1,2))+TIME(23,0,0),A1)

Then in cell to have the result
=IF(A1=A2,NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24),(B1-A2)*24)

--
Regards

Roger Govier


"BRO" wrote in message
...
The formula worked great. Any suggestions for when the start date
would fall
between 11 PM Friday night and 11 PM Sunday? On ocassion the start
date
would fall in this time period but the stop date would not. On these
ocassion the total time would start from 11 PM Sunday night.

Thanks,
--
BRO


"Roger Govier" wrote:

Hi

Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis
Toolpak, then

=NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
If you want to exclude Holidays as well as weekends, then add the 3rd
parameter to Networkdays which can either be cell references holding
a
list of holiday dates or a named range holding holiday dates

=NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
where X1:X9 hold a list of holiday dates
--
Regards

Roger Govier


"BRO" wrote in message
...
I am looking for a formula to calculate the difference between two
times in
hours. I also want to exclude the time between 11 PM on Friday
night
and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

--
BRO

Caluculate difference between two times
 

Sorry, should be multiplied by 24 as per original...

=(NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-NETWORKDAYS(A1+"1:00",A1+"1:00")*MOD(A1+"1:00",1)) *24


"daddylonglegs" wrote:

Hello BRO

"however on ocassion I do have
start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop
date however will never fall in this time period"

just amend my suggested formula to:

=NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-NETWORKDAYS(A1+"1:00",A1+"1:00")*MOD(A1+"1:00",1)

"Roger Govier" wrote:

Hi

I think the following will work, making use of a "helper cell". I used
A2.
In A2
=IF(OR(AND(WEEKDAY(A1,2)=5,MOD(A1,1)*24=23),
WEEKDAY(A1,2)5),DATE(YEAR(A1),
MONTH(A1),DAY(A1)+7-WEEKDAY(A1,2))+TIME(23,0,0),A1)

Then in cell to have the result
=IF(A1=A2,NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24),(B1-A2)*24)

--
Regards

Roger Govier


"BRO" wrote in message
...
The formula worked great. Any suggestions for when the start date
would fall
between 11 PM Friday night and 11 PM Sunday? On ocassion the start
date
would fall in this time period but the stop date would not. On these
ocassion the total time would start from 11 PM Sunday night.

Thanks,
--
BRO


"Roger Govier" wrote:

Hi

Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis
Toolpak, then

=NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
If you want to exclude Holidays as well as weekends, then add the 3rd
parameter to Networkdays which can either be cell references holding
a
list of holiday dates or a named range holding holiday dates

=NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
where X1:X9 hold a list of holiday dates
--
Regards

Roger Govier


"BRO" wrote in message
...
I am looking for a formula to calculate the difference between two
times in
hours. I also want to exclude the time between 11 PM on Friday
night
and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

--
BRO