I am trying to figure out how to subtract a cell containing a mm/dd/yyyy
hh:mm format from another cell with the same format and exclude the weekends.


Example:

A1(received): 7/28/06 15:28
A2(submitted): 7/31/06 16:07
A3 The difference in these two dates and times, excluding the weekend, to
determine the total turn around time.

The answer should reflect 1 day and 39 minutes in a format such as d hh:mm
or any other format that would make this easier.

I have the entire list of Excel functions and have tried all of them that I
think are logical but I can't seem to get the correct days and time brought
together into a single cell answer. Any help would be GREATLY appreciated!

I am using Excel 2003.

Thanks!

This should work option 31 days

=NETWORKDAYS(A1,A2)-1+MOD(A2,1)-MOD(A1,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Diane13" wrote in message
...
I am trying to figure out how to subtract a cell containing a mm/dd/yyyy
hh:mm format from another cell with the same format and exclude the
weekends.


Example:

A1(received): 7/28/06 15:28
A2(submitted): 7/31/06 16:07
A3 The difference in these two dates and times, excluding the weekend, to
determine the total turn around time.

The answer should reflect 1 day and 39 minutes in a format such as d hh:mm
or any other format that would make this easier.

I have the entire list of Excel functions and have tried all of them that
I
think are logical but I can't seem to get the correct days and time
brought
together into a single cell answer. Any help would be GREATLY
appreciated!

I am using Excel 2003.

Thanks!

that should be upto :-)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Diane13" wrote in message
...
I am trying to figure out how to subtract a cell containing a mm/dd/yyyy
hh:mm format from another cell with the same format and exclude the
weekends.


Example:

A1(received): 7/28/06 15:28
A2(submitted): 7/31/06 16:07
A3 The difference in these two dates and times, excluding the weekend, to
determine the total turn around time.

The answer should reflect 1 day and 39 minutes in a format such as d hh:mm
or any other format that would make this easier.

I have the entire list of Excel functions and have tried all of them that
I
think are logical but I can't seem to get the correct days and time
brought
together into a single cell answer. Any help would be GREATLY
appreciated!

I am using Excel 2003.

Thanks!

Bob, that worked great except I know have one minor quirk. When the
'received' date happens to fall on a weekend day, I (understandibly) get a
negative result (########). Is there anything I can add to the formula to
prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE
statement?

THANKS!


"Bob Phillips" wrote:

that should be upto :-)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Diane13" wrote in message
...
I am trying to figure out how to subtract a cell containing a mm/dd/yyyy
hh:mm format from another cell with the same format and exclude the
weekends.


Example:

A1(received): 7/28/06 15:28
A2(submitted): 7/31/06 16:07
A3 The difference in these two dates and times, excluding the weekend, to
determine the total turn around time.

The answer should reflect 1 day and 39 minutes in a format such as d hh:mm
or any other format that would make this easier.

I have the entire list of Excel functions and have tried all of them that
I
think are logical but I can't seem to get the correct days and time
brought
together into a single cell answer. Any help would be GREATLY
appreciated!

I am using Excel 2003.

Thanks!

I think I figured it out. This formula seems to be working -

=IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)))

Thanks for all your help Bob!!!


"Diane13" wrote:

Bob, that worked great except I know have one minor quirk. When the
'received' date happens to fall on a weekend day, I (understandibly) get a
negative result (########). Is there anything I can add to the formula to
prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE
statement?

THANKS!


"Bob Phillips" wrote:

that should be upto :-)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Diane13" wrote in message
...
I am trying to figure out how to subtract a cell containing a mm/dd/yyyy
hh:mm format from another cell with the same format and exclude the
weekends.


Example:

A1(received): 7/28/06 15:28
A2(submitted): 7/31/06 16:07
A3 The difference in these two dates and times, excluding the weekend, to
determine the total turn around time.

The answer should reflect 1 day and 39 minutes in a format such as d hh:mm
or any other format that would make this easier.

I have the entire list of Excel functions and have tried all of them that
I
think are logical but I can't seem to get the correct days and time
brought
together into a single cell answer. Any help would be GREATLY
appreciated!

I am using Excel 2003.

Thanks!

There seem to be various problems with weekend dates. This seems more robust

=NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I
2,1),0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Diane13" wrote in message
...
I think I figured it out. This formula seems to be working -


=IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1))
)

Thanks for all your help Bob!!!


"Diane13" wrote:

Bob, that worked great except I know have one minor quirk. When the
'received' date happens to fall on a weekend day, I (understandibly) get
a
negative result (########). Is there anything I can add to the formula
to
prevent these few instances? Possibly an Excel version of the IF, THEN,
ELSE
statement?

THANKS!


"Bob Phillips" wrote:

that should be upto :-)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Diane13" wrote in message
...
I am trying to figure out how to subtract a cell containing a
mm/dd/yyyy
hh:mm format from another cell with the same format and exclude the
weekends.


Example:

A1(received): 7/28/06 15:28
A2(submitted): 7/31/06 16:07
A3 The difference in these two dates and times, excluding the
weekend, to
determine the total turn around time.

The answer should reflect 1 day and 39 minutes in a format such as d
hh:mm
or any other format that would make this easier.

I have the entire list of Excel functions and have tried all of them
that
I
think are logical but I can't seem to get the correct days and time
brought
together into a single cell answer. Any help would be GREATLY
appreciated!

I am using Excel 2003.

Thanks!

That formula worked on everything but these four date ranges:

I J
8/19/2006 22:43 8/21/2006 8:22
7/30/2006 16:21 7/31/2006 15:33
7/29/2006 15:16 7/31/2006 7:48
8/27/2006 22:55 8/28/2006 9:17

These all resulted in 0 0:00 and I am not sure why. However, I have what I
need for my project so thank you so much for all your help!!! I would have
never figured out the MOD command on my own. Thanks!


"Bob Phillips" wrote:

There seem to be various problems with weekend dates. This seems more robust

=NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I
2,1),0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Diane13" wrote in message
...
I think I figured it out. This formula seems to be working -


=IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1))
)

Thanks for all your help Bob!!!


"Diane13" wrote:

Bob, that worked great except I know have one minor quirk. When the
'received' date happens to fall on a weekend day, I (understandibly) get
a
negative result (########). Is there anything I can add to the formula
to
prevent these few instances? Possibly an Excel version of the IF, THEN,
ELSE
statement?

THANKS!


"Bob Phillips" wrote:

that should be upto :-)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Diane13" wrote in message
...
I am trying to figure out how to subtract a cell containing a
mm/dd/yyyy
hh:mm format from another cell with the same format and exclude the
weekends.


Example:

A1(received): 7/28/06 15:28
A2(submitted): 7/31/06 16:07
A3 The difference in these two dates and times, excluding the
weekend, to
determine the total turn around time.

The answer should reflect 1 day and 39 minutes in a format such as d
hh:mm
or any other format that would make this easier.

I have the entire list of Excel functions and have tried all of them
that
I
think are logical but I can't seem to get the correct days and time
brought
together into a single cell answer. Any help would be GREATLY
appreciated!

I am using Excel 2003.

Thanks!