Excel 2003: Count total days elapsed

[watermt]

I need to count the total number of days (counting all days of the week)
elapsed between process 1 through 12. Using the m/d/yyyy hh:mm cell
formatting, not all 12 processes will have a date/time entered (blank cell).

When I use the following function; =NETWORKDAYS(F1,F12) and I have a blank
cell (i.e., F9) I get the #Name? error. Is there a way to have the function
ignore blank cells?

And finally, which function do I use to count all 7 days of the week?

Mike

 

[Jacob Skaria]

Use DATEDIF() to find difference between two dates

With start date in A1 and end date in B1
=DATEDIF(A1,B1,"d")

To find difference from today
A1 = start date
=TODAY()-A1
(and format to general)

If this post helps click Yes

 

[Shane Devenshire]

Hi,

Try:

=F12-F1

with the latest date in F12 and the earliest in F1. FYI NETWORKDAYS
subtracts out weekends. And it is an Analysis ToolPak function in 2003 so
you would need to attach it.

 

[David Biddulph]

I'd be surprised if the #NAME? error is related to blank cells.
It would be more likely to be because you have got ATP enabled. See Excel
help for the NETWORKDAYS function.

 

[watermt]

Yes, I have the Analysis ToolPak loaded. I thought Jacob had solved my
problem but after further computations it's not exactly what I needed.

Your formula would work (I think?!) if all cells between F1 through F12 had
a date/time entry - they do not and I think the problem is with the blank
cells.

I'm trying to set up this event monitoring function so the staff entering
date/time for each of the F1 through f12 events has does not have to change
the formula.

the cells contain the following in this event sample:

D1 - D12 E1 - E12 F1 - F12 G1 - G12
10/13/2008 21:00 Monday 10/13/2008 21:30 Monday
10/14/2008 11:00 Tuesday 10/14/2008 13:15 Tuesday
10/14/2008 14:15 Tuesday 10/14/2008 16:15 Tuesday
Saturday Saturday
Saturday Saturday
Saturday Saturday
10/14/2008 14:15 Tuesday 10/14/2008 16:15 Tuesday
Saturday Saturday
Saturday Saturday
Saturday Saturday
10/15/2008 12:00 Wednesday10/22/2008 13:15 Wednesday
Saturday Saturday

Notice in this 12 step process event there are no start or end date/time
entries for processes D4, D5, D6, D8, D9, D10 and d12 9they are blank). I
think this equates to roughly 176.0 hours, I need to calculate the number of
days (24 hour periods) and display the results as 7 days and 33 minutes.
Right now your suggestion gives me the ############ error depicting negative
dates.

Any suggestions,
Mike

 

[Jacob Skaria]

May be in Col H, cell H2 you can have the formula with a IF condition;;;

For hours
=IF(D2<>"",F2-D2,1)
(and format column H to [h]:mm:ss Right click>FormatCell>Custom Format)

copy that down as required..The sum of that will give you 176 hours....

If this post helps click Yes

 

[Ron Rosenfeld]

I need to count the total number of days (counting all days of the week)
elapsed between process 1 through 12. Using the m/d/yyyy hh:mm cell
formatting, not all 12 processes will have a date/time entered (blank cell).

When I use the following function; =NETWORKDAYS(F1,F12) and I have a blank
cell (i.e., F9) I get the #Name? error. Is there a way to have the function
ignore blank cells?

And finally, which function do I use to count all 7 days of the week?

Mike

Your questions and statements are confusing to me.

From your data layout and other comments, it appears as if you have start times
in ColD and ending times in ColF; and that you want the total of these elapsed
times.

It would be simple to set up another column (e.g. G) and have the elapsed time
formula in it:

G1: =F1-D1

To get the total:

=SUM(G1:G12)

The result will be in days and fractions of a day; in your example: 7.33333 (or
7 days 8 hours)

If you want to do all this in one formula:

This formula must be **array-entered**:

=SUM(F1:F12-D112)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[watermt]

Ron,
I apologize for the confusion. This project was passed on to me after an
employee left abruptly and I'm still working through the functions and
formulas. I do now have a column with this formula
=SUM(H17:OFFSET(H29,-1,0)) which gives me a total of 176:00 [hh]:mm. What I
need is to now get this converted to number of days the entire process takes.
I'm using this formula =H29/24 and I get an answer of 7:20 (which I believe
is 7 days and 33 minutes). But, how do I do I get that to display in the
cell in the 7 days 33 minutes format instead of 7:20 format? I presume I
need to change the Number tab in Cell Format but not sure what to change it
to?

Thanks,
Mike

 

[David Biddulph]

To convert your 176:00 to days you don't divide by 24, you merely format as
General or Number rather than as time. That will show as 7.33333 days (in
other words 7 days and 8 hours).
--
David Biddulph

Ron,
I apologize for the confusion. This project was passed on to me after an
employee left abruptly and I'm still working through the functions and
formulas. I do now have a column with this formula
=SUM(H17:OFFSET(H29,-1,0)) which gives me a total of 176:00 [hh]:mm. What
I
need is to now get this converted to number of days the entire process
takes.
I'm using this formula =H29/24 and I get an answer of 7:20 (which I
believe
is 7 days and 33 minutes). But, how do I do I get that to display in the
cell in the 7 days 33 minutes format instead of 7:20 format? I presume I
need to change the Number tab in Cell Format but not sure what to change
it
to?

Thanks,
Mike

Your questions and statements are confusing to me.

From your data layout and other comments, it appears as if you have start
times
in ColD and ending times in ColF; and that you want the total of these
elapsed
times.

It would be simple to set up another column (e.g. G) and have the elapsed
time
formula in it:

G1: =F1-D1

To get the total:

=SUM(G1:G12)

The result will be in days and fractions of a day; in your example:
7.33333 (or
7 days 8 hours)

If you want to do all this in one formula:

This formula must be **array-entered**:

=SUM(F1:F12-D112)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[watermt]

Thanks David, I really hate taking over other peoples projects, but it is
what it is! I'm learning this Excel stuff as quickly as I can and really
appreciate all the help this portal provides!

Mike

To convert your 176:00 to days you don't divide by 24, you merely format as
General or Number rather than as time. That will show as 7.33333 days (in
other words 7 days and 8 hours).
--
David Biddulph

Ron,
I apologize for the confusion. This project was passed on to me after an
employee left abruptly and I'm still working through the functions and
formulas. I do now have a column with this formula
=SUM(H17:OFFSET(H29,-1,0)) which gives me a total of 176:00 [hh]:mm. What
I
need is to now get this converted to number of days the entire process
takes.
I'm using this formula =H29/24 and I get an answer of 7:20 (which I
believe
is 7 days and 33 minutes). But, how do I do I get that to display in the
cell in the 7 days 33 minutes format instead of 7:20 format? I presume I
need to change the Number tab in Cell Format but not sure what to change
it
to?

Thanks,
Mike

On Mon, 15 Jun 2009 08:18:01 -0700, watermt
<[email protected]>
wrote:

I need to count the total number of days (counting all days of the week)
elapsed between process 1 through 12. Using the m/d/yyyy hh:mm cell
formatting, not all 12 processes will have a date/time entered (blank
cell).

When I use the following function; =NETWORKDAYS(F1,F12) and I have a
blank
cell (i.e., F9) I get the #Name? error. Is there a way to have the
function
ignore blank cells?

And finally, which function do I use to count all 7 days of the week?

Mike

Your questions and statements are confusing to me.

From your data layout and other comments, it appears as if you have start
times
in ColD and ending times in ColF; and that you want the total of these
elapsed
times.

It would be simple to set up another column (e.g. G) and have the elapsed
time
formula in it:

G1: =F1-D1

To get the total:

=SUM(G1:G12)

The result will be in days and fractions of a day; in your example:
7.33333 (or
7 days 8 hours)

If you want to do all this in one formula:

This formula must be **array-entered**:

=SUM(F1:F12-D112)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[Ron Rosenfeld]

Ron,
I apologize for the confusion. This project was passed on to me after an
employee left abruptly and I'm still working through the functions and
formulas. I do now have a column with this formula
=SUM(H17:OFFSET(H29,-1,0)) which gives me a total of 176:00 [hh]:mm. What I
need is to now get this converted to number of days the entire process takes.
I'm using this formula =H29/24 and I get an answer of 7:20 (which I believe
is 7 days and 33 minutes). But, how do I do I get that to display in the
cell in the 7 days 33 minutes format instead of 7:20 format? I presume I
need to change the Number tab in Cell Format but not sure what to change it
to?

Thanks,
Mike

OK, I think I understand what the problem is.

What you are not aware of is that Excel stores dates/times as days and
fractions of days.

As I wrote before, your values add up to 7.33333 or 7 days 8 hours.

When you display that result with a format of [h]:mm you see your 176:00. But
the underlying value is still 7.333333. Dividing 7.33333 by 24 is not a
meaningful operation in your context. But 7.33333 / 24 = 0.305556. Displaying
that value with a format of [h]:mm reveals 7:20 which is 7hrs 20min (or 7.3333
hours)

To obtain the display in days and hours, you could just custom format your
result as "d\d h\h" DON'T divide by 24.

The problem with doing that is that the "d" parameter cannot display more than
31, after which it "wraps around".

You could take your result and display it in a variety of ways, but the best
way probably depends on what you will be doing with this data.

If it is just for display, you could use a formula such as:

=INT(J13)&" days "&INT(MOD(J13,1)*24)&" hrs"

where J13 contains your SUM (or you could substitute your SUM formula for the
J13).
--ron

 

[watermt]

Ron,
I talked to the project leader and at this point the project team has
requested it merely be used as "display" of the length of time in days and
hours, so your formula will work perfedtly.

I only hope they do not at sometime in the future which to ude this reult in
another formula/function!

Thanks Ron!

Mike

Ron,
I apologize for the confusion. This project was passed on to me after an
employee left abruptly and I'm still working through the functions and
formulas. I do now have a column with this formula
=SUM(H17:OFFSET(H29,-1,0)) which gives me a total of 176:00 [hh]:mm. What I
need is to now get this converted to number of days the entire process takes.
I'm using this formula =H29/24 and I get an answer of 7:20 (which I believe
is 7 days and 33 minutes). But, how do I do I get that to display in the
cell in the 7 days 33 minutes format instead of 7:20 format? I presume I
need to change the Number tab in Cell Format but not sure what to change it
to?

Thanks,
Mike

OK, I think I understand what the problem is.

What you are not aware of is that Excel stores dates/times as days and
fractions of days.

As I wrote before, your values add up to 7.33333 or 7 days 8 hours.

When you display that result with a format of [h]:mm you see your 176:00. But
the underlying value is still 7.333333. Dividing 7.33333 by 24 is not a
meaningful operation in your context. But 7.33333 / 24 = 0.305556. Displaying
that value with a format of [h]:mm reveals 7:20 which is 7hrs 20min (or 7.3333
hours)

To obtain the display in days and hours, you could just custom format your
result as "d\d h\h" DON'T divide by 24.

The problem with doing that is that the "d" parameter cannot display more than
31, after which it "wraps around".

You could take your result and display it in a variety of ways, but the best
way probably depends on what you will be doing with this data.

If it is just for display, you could use a formula such as:

=INT(J13)&" days "&INT(MOD(J13,1)*24)&" hrs"

where J13 contains your SUM (or you could substitute your SUM formula for the
J13).
--ron

 

[Ron Rosenfeld]

Ron,
I talked to the project leader and at this point the project team has
requested it merely be used as "display" of the length of time in days and
hours, so your formula will work perfedtly.

I only hope they do not at sometime in the future which to ude this reult in
another formula/function!

Thanks Ron!

Mike

Glad to help. Thanks for the feedback.

You won't be able to use this result in a future calculation, but you could
either use a hidden cell, or substitute the SUM formula in the future
calculation matrix. (You could even write a formula to change that text back
to an Excel date/time value).
--ron