How do I convert...

[Guest]

I need a formula (function?), that converts a number - say...1022 - to a time
format like this: 10:22. Is this possible? TIA

 

[Guest]

if the number is in B1

=TIME(B1/100,MOD(B1,100),0)

note that this works in the 24 hour clock and does not deal with am/pm

if you want am pm then you need to adjust the hours by 12 by

=TIME(B1/100--(C1="PM")*12,MOD(B1,100),0)

assuming that AM or PM is in C1.

Hope this helps
Martin Fishlock
Please do not forget to rate this reply.

 

[Fred Smith]

Certainly it's possible.

Try something like:

=time(int(a1/100),mod(a1,100),0)

 

[Lori]

=--TEXT(A1,"00:00")

and make sure to format the result in time format.