Converting Numbers to Time

  • Thread starter Thread starter Smonczka
  • Start date Start date
S

Smonczka

I need help converting a number to a time value.

In one cell I have the number 10. I need to convert it to 00:10
minutes. Is this at all posible?

Thanks
Steve Monczka
(e-mail address removed)
 
Hi Steve

=A1/1440
format cell as time. (1440 is the number of minutes in a day.)

HTH. Best wishes Harald
 
Harald, I'm sorry I may not have been very clear in my original post.
What I am trying to do it convert the NUMBER 10 into the TIME 10
Minutes. I have to do calculations based on time. I have a list of
Start Times and End Times that are Time formated. Then I have a list
of numbers that are formated as NUMBERS that I need to format as
DATE/TIME so that I can use them in calculations with the first two
lists.

Is it posible to take the NUMBER 10 and convert it to a MINUTE format?

Thanks for your trouble,
Steve
 
My thanks to both of you. The artical helped alot. Now I get what the
formula does.

Thanks
Steve
 
That's what Harald's formula does. Times are stored as a
fraction of a 24 hour day (06:00:00 = 0.25, 12:00:00 = 0.5,
18:00:00 = 0.75, etc), so dividing the number 10 by the number of
minutes in a day returns a time value of 0.006944, which is equal
to 10 minutes. All you have to do is format this result in a time
format.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Steve

You didn't even test it because you didn't understand it. Shame on you. How
do you expect a computer to assist you under working conditions like that ?

Best wishes Harald
 
I did test it, but what I was missing was the fact that you actualy
produce different results due to the way the cell is formulated. So
when I first tried it out the cell was formated for General. This of
course totaly confused me and it wasn't till reading the artical I
figured out where my mistake was.

But I have to admit I'm pretty much of a nebie and this whole date/time
conversion process is very confusing so Harald, thank you for your help
on this. It is greatly appreciated.

Steve
 
Steve,

The formatting has nothing to do with the value of the cell (10 in this
case). Changing formats of numbers such as dates, or times, or percents, to
their more readable and user-friendly formats does not alter their values.
Not to beat a dead horse, that's why the formula suggested is required.

I have found the distinction between formatted value amd true value to be
key when using autofilters to filter a range of dates in a column. The
criteria has to be in terms of the serial number of the date (e.g., May 31,
2005 = 38503) and then autofilter can work with it. But ironically if you
check back to see what filter criteria are operative by using autofilter
menu item again, the criteria look like DATES!

Not to totally co-opt this thread but is there a reasonable explanation for
why excel assumes that result of adding or subtracting two dates should be
displayed as a date, instead of a whole number?

e.g. A1 holds the date 5/31/2005 and looks like the date
B1 holds the date 5/30/2005 and looks like the date
C1 has the formula "=A1 - B1"
Then C1 will have a value = 1 but be formatted as "1/1/1900"

Bill
 
Its probably that MS wanted the behaviour that when you add 1 to a date
it should give the next day's date. And this default behaviour is
applicable to the example you gave.

Mangesh
 
Back
Top