In an Excel 2003 spreadsheet, column D has the start date and time; D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and time; E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24). Column
G is then added to give a total of the days and hours. This formula is used,
=INT(SUM(G4:G49))&€� days, €œ&TEXT(MOD(SUM(G4:G49),1,€�h€�)&€� hours€�

It works fine except when the total number only has days and not any hours.
So if the total is 100 days and no hours, a €œvalue€� error is returned. If an
hour is added to the discontinue time it returns 100 days, 1 hour. How can
the formula be modified to show the zero hours when the column totals 100
days and no hours.

Thank you.

Why dont you use the custom format [h]:mm for column G. and use the normal
substraction =E1-D1 for difference and

for the total
use your formula to display in Days and hours

OR

use the SUM() function for the total..


If this post helps click Yes
---------------
Jacob Skaria


"Basenji" wrote:

In an Excel 2003 spreadsheet, column D has the start date and time; D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and time; E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24). Column
G is then added to give a total of the days and hours. This formula is used,
=INT(SUM(G4:G49))&€� days, €œ&TEXT(MOD(SUM(G4:G49),1,€�h€�)&€� hours€�

It works fine except when the total number only has days and not any hours.
So if the total is 100 days and no hours, a €œvalue€� error is returned. If an
hour is added to the discontinue time it returns 100 days, 1 hour. How can
the formula be modified to show the zero hours when the column totals 100
days and no hours.

Thank you.

When the sum function is used to give the sum of days and hours and the total
exceeds 31 days it returns an invalid number.

"Jacob Skaria" wrote:

Why dont you use the custom format [h]:mm for column G. and use the normal
substraction =E1-D1 for difference and

for the total
use your formula to display in Days and hours

OR

use the SUM() function for the total..


If this post helps click Yes
---------------
Jacob Skaria


"Basenji" wrote:

In an Excel 2003 spreadsheet, column D has the start date and time; D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and time; E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24). Column
G is then added to give a total of the days and hours. This formula is used,
=INT(SUM(G4:G49))&€� days, €œ&TEXT(MOD(SUM(G4:G49),1,€�h€�)&€� hours€�

It works fine except when the total number only has days and not any hours.
So if the total is 100 days and no hours, a €œvalue€� error is returned. If an
hour is added to the discontinue time it returns 100 days, 1 hour. How can
the formula be modified to show the zero hours when the column totals 100
days and no hours.

Thank you.

That formula doesn't have valid syntax, so Excel wouldn't have accepted it.
Without knowing what formula you are actusally using, we don't know how you
are getting your value error, but it is usually because you are putting text
into a function which wants a number.
When you post a message here, don't try to retype the formula. Copy the
formula from Excel's formula bar and paste it in here.

The formula, when you've corrected the unmatched parentheses, works fine
even if you have a total where the hours are zero.
--
David Biddulph

"Basenji" wrote in message
...
In an Excel 2003 spreadsheet, column D has the start date and time; D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and time;
E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24).
Column
G is then added to give a total of the days and hours. This formula is
used,
=INT(SUM(G4:G49))&" days, "&TEXT(MOD(SUM(G4:G49),1,"h")&" hours"

It works fine except when the total number only has days and not any
hours.
So if the total is 100 days and no hours, a "value" error is returned. If
an
hour is added to the discontinue time it returns 100 days, 1 hour. How can
the formula be modified to show the zero hours when the column totals 100
days and no hours.

Thank you.

Here is the formula copied from the cell: =INT(SUM(G4:G49))&" days,
"&TEXT(MOD(SUM(G4:G49),1),"h")&" hours"

Where am I missing the unmatched paranthesis?

"David Biddulph" wrote:

That formula doesn't have valid syntax, so Excel wouldn't have accepted it.
Without knowing what formula you are actusally using, we don't know how you
are getting your value error, but it is usually because you are putting text
into a function which wants a number.
When you post a message here, don't try to retype the formula. Copy the
formula from Excel's formula bar and paste it in here.

The formula, when you've corrected the unmatched parentheses, works fine
even if you have a total where the hours are zero.
--
David Biddulph

"Basenji" wrote in message
...
In an Excel 2003 spreadsheet, column D has the start date and time; D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and time;
E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24).
Column
G is then added to give a total of the days and hours. This formula is
used,
=INT(SUM(G4:G49))&" days, "&TEXT(MOD(SUM(G4:G49),1,"h")&" hours"

It works fine except when the total number only has days and not any
hours.
So if the total is 100 days and no hours, a "value" error is returned. If
an
hour is added to the discontinue time it returns 100 days, 1 hour. How can
the formula be modified to show the zero hours when the column totals 100
days and no hours.

Thank you.

Yes, that's OK, as you've included the parenthesis which was missing from
the formula you posted last time.
That formula doesn't return a value error if you have a time where the hours
are zero.
--
David Biddulph

"Basenji" wrote in message
...
Here is the formula copied from the cell: =INT(SUM(G4:G49))&" days,
"&TEXT(MOD(SUM(G4:G49),1),"h")&" hours"

Where am I missing the unmatched paranthesis?

"David Biddulph" wrote:

That formula doesn't have valid syntax, so Excel wouldn't have accepted
it.
Without knowing what formula you are actusally using, we don't know how
you
are getting your value error, but it is usually because you are putting
text
into a function which wants a number.
When you post a message here, don't try to retype the formula. Copy the
formula from Excel's formula bar and paste it in here.

The formula, when you've corrected the unmatched parentheses, works fine
even if you have a total where the hours are zero.
--
David Biddulph

"Basenji" wrote in message
...
In an Excel 2003 spreadsheet, column D has the start date and time; D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and
time;
E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and
rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24).
Column
G is then added to give a total of the days and hours. This formula is
used,
=INT(SUM(G4:G49))&" days, "&TEXT(MOD(SUM(G4:G49),1,"h")&" hours"

It works fine except when the total number only has days and not any
hours.
So if the total is 100 days and no hours, a "value" error is returned.
If
an
hour is added to the discontinue time it returns 100 days, 1 hour. How
can
the formula be modified to show the zero hours when the column totals
100
days and no hours.

Thank you.

rounds the time up or down to the closest half hour
G4= MROUND((E4-D4)1/24).

You're missing the argument separator but that's not rounding to the half
hour, it's rounding to the hour.

For the nearest half hour:

=MROUND(E4-D4,1/48)

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Yes, that's OK, as you've included the parenthesis which was missing from
the formula you posted last time.
That formula doesn't return a value error if you have a time where the
hours are zero.
--
David Biddulph

"Basenji" wrote in message
...
Here is the formula copied from the cell: =INT(SUM(G4:G49))&" days,
"&TEXT(MOD(SUM(G4:G49),1),"h")&" hours"

Where am I missing the unmatched paranthesis?

"David Biddulph" wrote:

That formula doesn't have valid syntax, so Excel wouldn't have accepted
it.
Without knowing what formula you are actusally using, we don't know how
you
are getting your value error, but it is usually because you are putting
text
into a function which wants a number.
When you post a message here, don't try to retype the formula. Copy the
formula from Excel's formula bar and paste it in here.

The formula, when you've corrected the unmatched parentheses, works fine
even if you have a total where the hours are zero.
--
David Biddulph

"Basenji" wrote in message
...
In an Excel 2003 spreadsheet, column D has the start date and time;
D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and
time;
E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and
rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24).
Column
G is then added to give a total of the days and hours. This formula is
used,
=INT(SUM(G4:G49))&" days, "&TEXT(MOD(SUM(G4:G49),1,"h")&" hours"

It works fine except when the total number only has days and not any
hours.
So if the total is 100 days and no hours, a "value" error is returned.
If
an
hour is added to the discontinue time it returns 100 days, 1 hour. How
can
the formula be modified to show the zero hours when the column totals
100
days and no hours.

Thank you.

Thank you for the clarification.

"T. Valko" wrote:

rounds the time up or down to the closest half hour
G4= MROUND((E4-D4)1/24).

You're missing the argument separator but that's not rounding to the half
hour, it's rounding to the hour.

For the nearest half hour:

=MROUND(E4-D4,1/48)

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Yes, that's OK, as you've included the parenthesis which was missing from
the formula you posted last time.
That formula doesn't return a value error if you have a time where the
hours are zero.
--
David Biddulph

"Basenji" wrote in message
...
Here is the formula copied from the cell: =INT(SUM(G4:G49))&" days,
"&TEXT(MOD(SUM(G4:G49),1),"h")&" hours"

Where am I missing the unmatched paranthesis?

"David Biddulph" wrote:

That formula doesn't have valid syntax, so Excel wouldn't have accepted
it.
Without knowing what formula you are actusally using, we don't know how
you
are getting your value error, but it is usually because you are putting
text
into a function which wants a number.
When you post a message here, don't try to retype the formula. Copy the
formula from Excel's formula bar and paste it in here.

The formula, when you've corrected the unmatched parentheses, works fine
even if you have a total where the hours are zero.
--
David Biddulph

"Basenji" wrote in message
...
In an Excel 2003 spreadsheet, column D has the start date and time;
D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and
time;
E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and
rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24).
Column
G is then added to give a total of the days and hours. This formula is
used,
=INT(SUM(G4:G49))&" days, "&TEXT(MOD(SUM(G4:G49),1,"h")&" hours"

It works fine except when the total number only has days and not any
hours.
So if the total is 100 days and no hours, a "value" error is returned.
If
an
hour is added to the discontinue time it returns 100 days, 1 hour. How
can
the formula be modified to show the zero hours when the column totals
100
days and no hours.

Thank you.

Thank you. It is working properly now.

"David Biddulph" wrote:

Yes, that's OK, as you've included the parenthesis which was missing from
the formula you posted last time.
That formula doesn't return a value error if you have a time where the hours
are zero.
--
David Biddulph

"Basenji" wrote in message
...
Here is the formula copied from the cell: =INT(SUM(G4:G49))&" days,
"&TEXT(MOD(SUM(G4:G49),1),"h")&" hours"

Where am I missing the unmatched paranthesis?

"David Biddulph" wrote:

That formula doesn't have valid syntax, so Excel wouldn't have accepted
it.
Without knowing what formula you are actusally using, we don't know how
you
are getting your value error, but it is usually because you are putting
text
into a function which wants a number.
When you post a message here, don't try to retype the formula. Copy the
formula from Excel's formula bar and paste it in here.

The formula, when you've corrected the unmatched parentheses, works fine
even if you have a total where the hours are zero.
--
David Biddulph

"Basenji" wrote in message
...
In an Excel 2003 spreadsheet, column D has the start date and time; D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and
time;
E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and
rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24).
Column
G is then added to give a total of the days and hours. This formula is
used,
=INT(SUM(G4:G49))&" days, "&TEXT(MOD(SUM(G4:G49),1,"h")&" hours"

It works fine except when the total number only has days and not any
hours.
So if the total is 100 days and no hours, a "value" error is returned.
If
an
hour is added to the discontinue time it returns 100 days, 1 hour. How
can
the formula be modified to show the zero hours when the column totals
100
days and no hours.

Thank you.