Military Time to normal Time

S

Steved

Hello from Steved

I have a conveter in VBA to change Military to normal Time.

In cell C8 I type 1440 it will return 2:30 p.m.

Is it possible to type in Cell C8 1440 it will reurn 2:30
and in Cell D8 it will enter P for after 12:00 p.m., or if
I type 800 it will return 8:00 in Column C and A in Column
D

Thankyou
 
J

Jason Morin

To convert to XL time, use this:

=TEXT(C8,"00\:00")*1

To return "P" for PM and "A" for AM:

=CHOOSE(MATCH(--(TEXT(C8,"00\:00")*1>=0.5),
{1,0},0),"P","A")

HTH
Jason
Atlanta, GA
 
S

Steved

Hello Jason from Steved

Jason firstly Thankyou
Using your formula below i would like to type 1440 to 2:40
as at the moment it will return 14:40

=TEXT(C8,"00\:00")*1

ok is this possible 1440 to 2:40 in cell C8 and applying
your second formula puts in this case "P" in D8

Thanks for what you have given me so far.
 
C

Captain_Nemo

SteveD -

Another approach:

=INT(IF(C8>1200,C8-1200,C8)/100)&":"&MOD(C8,100)

AND

=IF(C8>=1200,"P","A") for AM or PM

....best, Capt N.

Steved said:
Hello Jason from Steved

Jason firstly Thankyou
Using your formula below i would like to type 1440 to 2:40
as at the moment it will return 14:40

=TEXT(C8,"00\:00")*1

ok is this possible 1440 to 2:40 in cell C8 and applying
your second formula puts in this case "P" in D8

Thanks for what you have given me so far.
Click to expand...

--
Email to [email protected] (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy
 
C

Captain_Nemo

The first formula gives the wrong answer if time is between 1200 and
1300 military time, or between 0000 and 0100. It should be:

=INT(IF(C8>=1260,C8-1200,IF(C8<100,1200,C8))/100)&":"&MOD(C8,100)

SteveD -

Another approach:

=INT(IF(C8>1200,C8-1200,C8)/100)&":"&MOD(C8,100)

AND

=IF(C8>=1200,"P","A") for AM or PM

...best, Capt N.
Click to expand...

--
Email to [email protected] (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy
 
C

Captain_Nemo

Sigh. And the back end should be &TEXT(MOD(C8,100),"00")

I shouldn't do this on the fly.

....best, Capt N.

The first formula gives the wrong answer if time is between 1200 and
1300 military time, or between 0000 and 0100. It should be:

=INT(IF(C8>=1260,C8-1200,IF(C8<100,1200,C8))/100)&":"&MOD(C8,100)
Click to expand...

--
Email to [email protected] (yes, you can so figure it out) ;-]

Scream and shout and jump for joy! I was here before Kilroy!

Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy
 
Top