change general format to time w/o losing the data

K

klaire

Help - I have several spreadsheet with military time i.e.
1701, 1615, etc. The cells are in general format. Since
I need to calculate hours from these times, I tried to
convert them to [h]:mm but when I do, it changed the
number 1701 to 40824:00. I tried to change the cell format
to hh:mm but it gave me zeros. I use Excel 2000. Thanks.
 
J

JulieD

Hi

two options - to convert the 1701 or whatever to "time" try
=TIME(INT(A1/100),MOD(A1,100),0)
where the A1 has 1701 in it

also you can calculate with your original format of time in the following
way:
with your start time in A1 (0800)
your end time in A2 (1730)
in A3 the formula to determine the number of hours between them would be
=DOLLARDE(A2/100,60)-DOLLARDE(A1/100,60)

Hope this helps
Cheers
JulieD
 
K

klaire

Thank you!!
-----Original Message-----
Hi

With your number in cell A2:
=TIME(LEFT(A1,2),RIGHT(A1,2),0)


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)


Help - I have several spreadsheet with military time i.e.
1701, 1615, etc. The cells are in general format. Since
I need to calculate hours from these times, I tried to
convert them to [h]:mm but when I do, it changed the
number 1701 to 40824:00. I tried to change the cell format
to hh:mm but it gave me zeros. I use Excel 2000.
Click to expand...
Thanks.


.
Click to expand...
 
D

Dave Peterson

And one more:

=TIMEVALUE(TEXT(A1,"00\:00"))

And format it the way you like: [h]:mm


Help - I have several spreadsheet with military time i.e.
1701, 1615, etc. The cells are in general format. Since
I need to calculate hours from these times, I tried to
convert them to [h]:mm but when I do, it changed the
number 1701 to 40824:00. I tried to change the cell format
to hh:mm but it gave me zeros. I use Excel 2000. Thanks.
Click to expand...
 
Top