How to convert: (1) 6:00 AM to 0600; and (2) 8:00 PM to 2000?

[matthew.b.strauss]

I have cells in Excel that are formatted as '1:30 PM', and I want to know how to convert: (1) '6:00 AM' to '0600'; and (2) '8:00 PM' to '2000'?

I've spent a lot of time trying to figure this out, but I don't know how to add a '0' before the 600, nor do I know how to get rid of the colon. Thank you for your help.

[Special-K]

=TEXT(HOUR(A1)*100+MINUTE(A1),"0000")

This will change the value to text 0600

or change the format of the cell to Custom using hhmm though this will not change the value to 600, only the display.
600 is 0.25 in Excel time so the value will still be 0.25 not 600.

[Ghozi Alkatiri]

=text(hour(a1)*100,"0000")

[Special-K]

=text(hour(a1)*100,"0000")

This doesn't work if minutes/seconds are also entered.

[davsth]

it is difficult to know if the cells are date/times or text
=TEXT(a1,"HHMM")
=LEFT(F9,SEARCH(":",F9,1)-1)*100+IF(RIGHT(F9,2)="AM",0,1200)+MID(F9,SEARCH(":",F9,1)+1,2) and custom format cell as 0000

[kvsrinivasamurthy]

If D23=6:00 am then

=TEXT(HOUR(D23),"00")&TEXT(MINUTE(D23),"00")

[XLent]

it is difficult to know if the cells are date/times or text
=TEXT(a1,"HHMM")

Hi, the above would work for both datetime and datetime stored as text; and is the optimal solution.

[matthew.b.strauss]

Thank you to everyone for helping solve this issue: (1) Special-K; (2) Ghozi Alkatiri; (3) davsth; (4) kvsrinivasamurthy; and (5) XLent. You are all awesome and super knowledgeable. It really means a lot to me and I appreciate the prompt responses.