How can I convert This

J

John

How can I convert 7:00 PM (or 0.791666666666667) to 1900 (this has a general
format)

My problem is that I import a database from MS Access and its time format is
as the example above shows. Thus I'm looking for a formula that can convert
my Times eg 6:15 AM to 615 etc


Thanks
 
D

David Biddulph

John said:
How can I convert 7:00 PM (or 0.791666666666667) to 1900 (this has a
general format)

My problem is that I import a database from MS Access and its time format
is as the example above shows. Thus I'm looking for a formula that can
convert my Times eg 6:15 AM to 615 etc
Click to expand...

If you just want to display it that way, Format Cells/ Custom
hhmm

If you actually want to convert the number to that format, then you'll need
something like
=HOUR(B1)*100+MINUTE(B1)
but of course you'll need to be careful as you couldn't sensibly add 620 to
750 in that way.

Another option is =TEXT(B1,"hhmm"), but you'd still need to be cautious that
you didn't try to do arithmetic on the text cells as if they were sensible
numbers.
 
J

John

Daivd, I've used =HOUR(B1)*100+MINUTE(B1) suggestion and it seems to work
fine

Thanks


John said:
Thanks David, something to think about. I'll test it later today using
your options
Click to expand...
 
Top