Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default subtracting date and time formats excluding weekends

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!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default subtracting date and time formats excluding weekends

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!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default subtracting date and time formats excluding weekends

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!



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default subtracting date and time formats excluding weekends

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!




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default subtracting date and time formats excluding weekends

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!






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default subtracting date and time formats excluding weekends

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!






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default subtracting date and time formats excluding weekends

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!







  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 380
Default subtracting date and time formats excluding weekends

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!









  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default subtracting date and time formats excluding weekends

Hi Bob,

When I try this formula I get a name error. do you know why?

"Bob Phillips" wrote:

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!










  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,104
Default subtracting date and time formats excluding weekends

Form XL 2003 Help when I search NETWORKDAYS:
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

Please come back if more info needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Hi Bob,

When I try this formula I get a name error. do you know why?

"Bob Phillips" wrote:

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!













  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default subtracting date and time formats excluding weekends

Thank you...you're right, I forgot about that and I was recently upgrated to
Excel 2003. I still have the name error..would I need to reboot?

"Bernard Liengme" wrote:

Form XL 2003 Help when I search NETWORKDAYS:
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

Please come back if more info needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Hi Bob,

When I try this formula I get a name error. do you know why?

"Bob Phillips" wrote:

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!












  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default subtracting date and time formats excluding weekends

Thank you so much...please disregard my previous email. When I re-typed the
formula, the name error went away however if the results are outside of 24
hours, I get ############ across the cells. Is there a special formatting I
should be using?

"Bernard Liengme" wrote:

Form XL 2003 Help when I search NETWORKDAYS:
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

Please come back if more info needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Hi Bob,

When I try this formula I get a name error. do you know why?

"Bob Phillips" wrote:

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!












  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,104
Default subtracting date and time formats excluding weekends

Try Tools | Add-in and if there is an Analysis Toolpak entry, then check its
box.
Then just continue with Excel session

If there is no entry: close Excel and use Control Panel | Add Delete
Programs to change the Office 2003 install such that Analysis Toolpak gets
installed -- might need the disk , can't remember. Restart Excel and use
Tools | Add-in as above

Never a need to reboot
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Thank you...you're right, I forgot about that and I was recently upgrated
to
Excel 2003. I still have the name error..would I need to reboot?

"Bernard Liengme" wrote:

Form XL 2003 Help when I search NETWORKDAYS:
If this function is not available, and returns the #NAME? error, install
and
load the Analysis ToolPak add-in.

Please come back if more info needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Hi Bob,

When I try this formula I get a name error. do you know why?

"Bob Phillips" wrote:

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!













  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default subtracting date and time formats excluding weekends

Since you're working with =networkdays(), I would think that General (or some
variation of a Number) would be best.

But try widening the column first. Maybe you just can't see what it's trying to
display (if you have either an error or a number in that cell and the column is
too narrow, you'll see those #####'s).

Colleen10 wrote:

Thank you so much...please disregard my previous email. When I re-typed the
formula, the name error went away however if the results are outside of 24
hours, I get ############ across the cells. Is there a special formatting I
should be using?

"Bernard Liengme" wrote:

Form XL 2003 Help when I search NETWORKDAYS:
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

Please come back if more info needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Hi Bob,

When I try this formula I get a name error. do you know why?

"Bob Phillips" wrote:

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!













--

Dave Peterson
  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default subtracting date and time formats excluding weekends

Thanks I formatted to general however I did not get the desired results of
displaying the difference...for example, I want to see the difference to
display in hours and minutes. For example 5/19/09 14:15 and 5/19/09 16:56
shud display in a format of 2:41 (2 hours, 41 minutes) or dates if were
5/22/09 9:00 and 5/24/09 9:00 should display as 48:00 (48 hours, zero mins)
Is this even possible?

ave Peterson" wrote:

Since you're working with =networkdays(), I would think that General (or some
variation of a Number) would be best.

But try widening the column first. Maybe you just can't see what it's trying to
display (if you have either an error or a number in that cell and the column is
too narrow, you'll see those #####'s).

Colleen10 wrote:

Thank you so much...please disregard my previous email. When I re-typed the
formula, the name error went away however if the results are outside of 24
hours, I get ############ across the cells. Is there a special formatting I
should be using?

"Bernard Liengme" wrote:

Form XL 2003 Help when I search NETWORKDAYS:
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

Please come back if more info needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Hi Bob,

When I try this formula I get a name error. do you know why?

"Bob Phillips" wrote:

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!













--

Dave Peterson



  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,104
Default subtracting date and time formats excluding weekends

Send me a sample worksheet
Please get my email from my website
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Thanks I formatted to general however I did not get the desired results of
displaying the difference...for example, I want to see the difference to
display in hours and minutes. For example 5/19/09 14:15 and 5/19/09 16:56
shud display in a format of 2:41 (2 hours, 41 minutes) or dates if were
5/22/09 9:00 and 5/24/09 9:00 should display as 48:00 (48 hours, zero
mins)
Is this even possible?

ave Peterson" wrote:

Since you're working with =networkdays(), I would think that General (or
some
variation of a Number) would be best.

But try widening the column first. Maybe you just can't see what it's
trying to
display (if you have either an error or a number in that cell and the
column is
too narrow, you'll see those #####'s).

Colleen10 wrote:

Thank you so much...please disregard my previous email. When I
re-typed the
formula, the name error went away however if the results are outside of
24
hours, I get ############ across the cells. Is there a special
formatting I
should be using?

"Bernard Liengme" wrote:

Form XL 2003 Help when I search NETWORKDAYS:
If this function is not available, and returns the #NAME? error,
install and
load the Analysis ToolPak add-in.

Please come back if more info needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Colleen10" wrote in message
...
Hi Bob,

When I try this formula I get a name error. do you know why?

"Bob Phillips" wrote:

I told you weekedns were a problem <bg

I think this works for all situations

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Diane13" wrote in message
...
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!













--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate A Date Excluding Weekends And Holidays travelersway New Users to Excel 10 February 9th 12 08:34 PM
Deleting time part of a Date, subtracting dates Shirley Munro Excel Discussion (Misc queries) 3 June 21st 06 11:58 AM
Calculate A Date Excluding Weekends And Holidays travelersway New Users to Excel 1 February 8th 06 09:56 PM
Using VLOOKUP with a Date and Time Charles Excel Discussion (Misc queries) 4 September 20th 05 06:38 PM
Subtracting time in date format Collcat Excel Worksheet Functions 1 November 10th 04 05:45 PM


All times are GMT +1. The time now is 03:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"