Time calculations?

[Blue Max]

Does anyone have a recommended link for understanding the Excel 2007 time
functions. Here are some of the questions we have:

FIRST, it appears that the decimal .99999999 represents 24 hours. Yet cell
formatted for time will also display 1.00000000 as 24 hours. So, can the
two be used interchangeably or is there a finer difference we have not
recognized? Presumably, one of these values will misrepresent accumulations
of time as the quantity increases.

SECOND, what is the exact decimal representation for 1 hour, 1 minute, or 1
second? Again .00069999 and .0007 appear to represent one (1) minute. But
formulas that accumulate minutes could be erroneous depending on which value
we use.

THIRD, we are accumulating counts where the value "1" equals 20 minutes. We
need to multiply the count by 20 to calculate the total minutes and then we
need to convert the number of minutes to a time value that can be displayed
in total hours and minutes. Initially, we used the TIME() function but
found that it would not accumulate time beyond 24 hours. We later found
that a decimal value in a cell formated for time as [hh]" Hours "mm" Minutes
" would properly accumulate the time in hours and minutes, but we are not
sure what value to use for minutes?

Thank you for any assistance.

 

[Harald Staff]

This is good:
http://www.cpearson.com/excel/datetime.htm

Basics:
1 is a day, 24 hours.
1 hour is 1/24
1 minute is 1/(24*60) = 1/1440
1 second is 1/(24*60*60) = 1/86400

As for the differences between 1 and 0.999999..., you may want to read about
rounding errors caused by decimal/binary conversion:
http://www.cpearson.com/excel/rounding.htm
I assume small rounding errors will round to time's nearest second, the
nearest 1/86400.

Your 20 minute units will be real time this way:
=A1/(1440/20) ,which is
=A1/72
format as [hh]:mm

HTH. Best wishes Harald

 

[Pete_UK]

Dates are stored internally in Excel as the number of elapsed days
since some reference date, where 1 = 1st Jan 1900. Thus dates/days are
integers.

Time is stored internally as a fraction of a 24-hour day, so 12:00
noon is stored as 0.5, 6:00 am as 0.25 etc. Conversions to/from
integer hours or minutes or seconds can be accomplished by multiplying/
dividing by 24, 60 or 60 respectively.

You should use 1.0000000000 to represent 24 hours exactly.

1 hour is equal to 1/24, or 0.0416666666666667

1 minute is 1/24/60, or 0.000694444444444444

1 second is 1/24/60/60, or 0.0000115740740740741

shown to Excel's degree of accuracy, but you can see that these are
still approximations.

As times are just numbers to Excel, they can be added and subtracted.
If an addition results in a value greater than 1, then Excel will
normally display this as days plus the remaining time - as you have
discovered, applying a custom format to the cell of [hh]:mm will cause
Excel not to wrap the display beyond 24 hours. Similarly, a custom
format of [mm]:ss will cause the minutes to display rather than hours,
so that you could have something like 61:00 for 61 minutes rather than
1:01:00.

Hope this helps.

Pete

 

[Blue Max]

Thank you, Harald. Some great information. I actually stumbled across the
24, 1440, and 86400 divisors after posting this message and implemented
those divisors to solve our problem. I especially appreciated how you
summarized these ratios as divisors of 1 versus as a long decimal fractions
(which was where I was inadvertantly headed). It really helped clarify the
math at work behind the scenes. Thank you for responding.

Richard Wilkinson

******************

This is good:
http://www.cpearson.com/excel/datetime.htm

Basics:
1 is a day, 24 hours.
1 hour is 1/24
1 minute is 1/(24*60) = 1/1440
1 second is 1/(24*60*60) = 1/86400

As for the differences between 1 and 0.999999..., you may want to read
about rounding errors caused by decimal/binary conversion:
http://www.cpearson.com/excel/rounding.htm
I assume small rounding errors will round to time's nearest second, the
nearest 1/86400.

Your 20 minute units will be real time this way:
=A1/(1440/20) ,which is
=A1/72
format as [hh]:mm

HTH. Best wishes Harald

Does anyone have a recommended link for understanding the Excel 2007 time
functions. Here are some of the questions we have:

FIRST, it appears that the decimal .99999999 represents 24 hours. Yet
cell formatted for time will also display 1.00000000 as 24 hours. So,
can the two be used interchangeably or is there a finer difference we
have not recognized? Presumably, one of these values will misrepresent
accumulations of time as the quantity increases.

SECOND, what is the exact decimal representation for 1 hour, 1 minute, or
1 second? Again .00069999 and .0007 appear to represent one (1) minute.
But formulas that accumulate minutes could be erroneous depending on
which value we use.

THIRD, we are accumulating counts where the value "1" equals 20 minutes.
We need to multiply the count by 20 to calculate the total minutes and
then we need to convert the number of minutes to a time value that can be
displayed in total hours and minutes. Initially, we used the TIME()
function but found that it would not accumulate time beyond 24 hours. We
later found that a decimal value in a cell formated for time as [hh]"
Hours "mm" Minutes " would properly accumulate the time in hours and
minutes, but we are not sure what value to use for minutes?

Thank you for any assistance.

 

[Blue Max]

Thank you, Pete, for a very clear summary of making the transition from the
decimal fractions to the ratios - a great help. I also stumbled across the
24, 1440 (24 x 60), and 86400 (24 x 60 x 60) divisors used to convert
accumulations to hours, minutes, and seconds and used that to resolve our
problem. I especially appreciated how you summarized these ratios as
divisors of 1, including the long decimal fractions. It really helped
clarify the math at work behind the scenes.

The only remaining question for me, is why does the online Office help
characterize 24 hours as .99999999 versus 1, as you did? I think that your
approach is much more intuitive than the example in help. Thank you for
responding.

Richard Wilkinson

******************
Dates are stored internally in Excel as the number of elapsed days
since some reference date, where 1 = 1st Jan 1900. Thus dates/days are
integers.

Time is stored internally as a fraction of a 24-hour day, so 12:00
noon is stored as 0.5, 6:00 am as 0.25 etc. Conversions to/from
integer hours or minutes or seconds can be accomplished by multiplying/
dividing by 24, 60 or 60 respectively.

You should use 1.0000000000 to represent 24 hours exactly.

1 hour is equal to 1/24, or 0.0416666666666667

1 minute is 1/24/60, or 0.000694444444444444

1 second is 1/24/60/60, or 0.0000115740740740741

shown to Excel's degree of accuracy, but you can see that these are
still approximations.

As times are just numbers to Excel, they can be added and subtracted.
If an addition results in a value greater than 1, then Excel will
normally display this as days plus the remaining time - as you have
discovered, applying a custom format to the cell of [hh]:mm will cause
Excel not to wrap the display beyond 24 hours. Similarly, a custom
format of [mm]:ss will cause the minutes to display rather than hours,
so that you could have something like 61:00 for 61 minutes rather than
1:01:00.

Hope this helps.

Pete

Does anyone have a recommended link for understanding the Excel 2007 time
functions. Here are some of the questions we have:

FIRST, it appears that the decimal .99999999 represents 24 hours. Yet cell
formatted for time will also display 1.00000000 as 24 hours. So, can the
two be used interchangeably or is there a finer difference we have not
recognized? Presumably, one of these values will misrepresent
accumulations
of time as the quantity increases.

SECOND, what is the exact decimal representation for 1 hour, 1 minute, or
1
second? Again .00069999 and .0007 appear to represent one (1) minute. But
formulas that accumulate minutes could be erroneous depending on which
value
we use.

THIRD, we are accumulating counts where the value "1" equals 20 minutes.
We
need to multiply the count by 20 to calculate the total minutes and then
we
need to convert the number of minutes to a time value that can be
displayed
in total hours and minutes. Initially, we used the TIME() function but
found that it would not accumulate time beyond 24 hours. We later found
that a decimal value in a cell formated for time as [hh]" Hours "mm"
Minutes
" would properly accumulate the time in hours and minutes, but we are not
sure what value to use for minutes?

Thank you for any assistance.

 

[Pete_UK]

If you take 1 second away from 24 hours (i.e. 1 -
0.0000115740740740741), you get 0.999988425925926.

Obviously, 0.99999999 is closer to 1 than to this number, so it would
still be interpreted as 24 hours.

Hope this helps.

Pete

Thank you, Pete, for a very clear summary of making the transition from the
decimal fractions to the ratios - a great help.  I also stumbled acrossthe
24, 1440 (24 x 60), and 86400 (24 x 60 x 60) divisors used to convert
accumulations to hours, minutes, and seconds and used that to resolve our
problem.  I especially appreciated how you summarized these ratios as
divisors of 1, including the long decimal fractions.  It really helped
clarify the math at work behind the scenes.

The only remaining question for me, is why does the online Office help
characterize 24 hours as .99999999 versus 1, as you did?  I think that your
approach is much more intuitive than the example in help.  Thank you for
responding.

Richard Wilkinson


Dates are stored internally in Excel as the number of elapsed days
since some reference date, where 1 = 1st Jan 1900. Thus dates/days are
integers.

Time is stored internally as a fraction of a 24-hour day, so 12:00
noon is stored as 0.5, 6:00 am as 0.25 etc. Conversions to/from
integer hours or minutes or seconds can be accomplished by multiplying/
dividing by 24, 60 or 60 respectively.

You should use 1.0000000000 to represent 24 hours exactly.

1 hour is equal to 1/24, or 0.0416666666666667

1 minute is 1/24/60, or 0.000694444444444444

1 second is 1/24/60/60, or 0.0000115740740740741

shown to Excel's degree of accuracy, but you can see that these are
still approximations.

As times are just numbers to Excel, they can be added and subtracted.
If an addition results in a value greater than 1, then Excel will
normally display this as days plus the remaining time - as you have
discovered, applying a custom format to the cell of [hh]:mm will cause
Excel not to wrap the display beyond 24 hours. Similarly, a custom
format of [mm]:ss will cause the minutes to display rather than hours,
so that you could have something like 61:00 for 61 minutes rather than
1:01:00.

Hope this helps.

Pete

Does anyone have a recommended link for understanding the Excel 2007 time
functions. Here are some of the questions we have:

FIRST, it appears that the decimal .99999999 represents 24 hours. Yet cell
formatted for time will also display 1.00000000 as 24 hours. So, can the
two be used interchangeably or is there a finer difference we have not
recognized? Presumably, one of these values will misrepresent
accumulations
of time as the quantity increases.

SECOND, what is the exact decimal representation for 1 hour, 1 minute, or
1
second? Again .00069999 and .0007 appear to represent one (1) minute. But
formulas that accumulate minutes could be erroneous depending on which
value
we use.

THIRD, we are accumulating counts where the value "1" equals 20 minutes..
We
need to multiply the count by 20 to calculate the total minutes and then
we
need to convert the number of minutes to a time value that can be
displayed
in total hours and minutes. Initially, we used the TIME() function but
found that it would not accumulate time beyond 24 hours. We later found
that a decimal value in a cell formated for time as [hh]" Hours "mm"
Minutes
" would properly accumulate the time in hours and minutes, but we are not
sure what value to use for minutes?

Thank you for any assistance.- Hide quoted text -

- Show quoted text -