hour format

[galin]

Guys,
What about the hour format.

I have 93000 or 181500

and I need it to be

9:30 or 18:15

Thanks

and I really tried to find a solution before posting the message

 

[Frank Kabel]

Hi
try the following formula
=TIME(LEFT(A1,1+LEN(A1)=6),MID(A1,2+LEN(A1)=6,2,RIGHT(A1,2))

 

[Peo Sjoblom]

One way using a help column assuming that your times when less than 10:00
has 5 digits and when
greater has 6 digits

Assume they start in A1

=(INT(A1/10000)+MOD(A1/100,100)/60)/24

copy down, paste special as values in place and format as hh:mm
if you can have numbers like 930 or 1815 as well you can use

=IF(LEN(A1)<=4,(INT(A1/100)+MOD(A1,100)/60)/24,(INT(A1/10000)+MOD(A1/100,100
)/60)/24)

note that it important to format as time or else you will get decimal values

 

[galin]

I'm sorry,

I can't figure it out. Maybe I am not doing it right.

Let me figth , we'll see what happens.

Gali

 

[terre08]

*I'm sorry,

I can't figure it out. Maybe I am not doing it right.

Let me figth , we'll see what happens.

Galin *

I have attached a sample here


Regards,

Peo Sjoblo

 

[Peo Sjoblom]

Oops!

http://terre08.home.mindspring.com/test_for_time.xls

right click link and select save target..

 

[galin]

Thanks for the help Peo,

You have been magnificient.

I am fine with my excel spreadsheets now, but I have encountere
another problem now. When I import the table with the hours time fro
Excel to Access I am losing the format. Basically I need to have Acces
run query on the hour table in format understandable for the staff an
also looking good in printed report form.

Do you have any suggestions?

Thanks ,Galin

Don't feel obligated to respond me. You've done enoug