Military Time to normal Time

[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

 

[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

 

[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.

 

[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.

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.

--
Email to (e-mail address removed) (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

 

[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.

--
Email to (e-mail address removed) (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

 

[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)

--
Email to (e-mail address removed) (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