View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John
 
Posts: n/a
Default How can I convert This

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