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]

"John" <[email protected]> wrote in message
news:[email protected]...
> 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

[John]

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



"David Biddulph" <[email protected]> wrote in message
news:[email protected]...
> "John" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>

[John]

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

Thanks


"John" <[email protected]> wrote in message
news:[email protected]...
> Thanks David, something to think about. I'll test it later today using
> your options
>
>
>
> "David Biddulph" <[email protected]> wrote in message
> news:[email protected]...
>> "John" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>
>