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

"John" wrote in message
...
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.
--
David Biddulph

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



"David Biddulph" wrote in message
...
"John" wrote in message
...
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.
--
David Biddulph

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

Thanks


"John" wrote in message
...
Thanks David, something to think about. I'll test it later today using
your options



"David Biddulph" wrote in message
...
"John" wrote in message
...
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.
--
David Biddulph