How can I convert This

[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

 

[David Biddulph]

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

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.

 

[John]

Thanks David, something to think about. I'll test it later today using your
options

 

[John]

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

Thanks

Thanks David, something to think about. I'll test it later today using
your options