Need help with the following function...

[anjelica]

I have used a formula to round down 11:45AM to 11:00AM but now I want to Convert that rounded time of 11:00AM to 1100a. I'm tried substitute function but keep getting a decimal number or an error.

Please help.

Thanks,
anjelica

[63falcondude]

Hello and welcome to the forum.

Are you looking to convert the time (a numeric value) into text that represents a time or do you just want to format the time to look like that?

[anjelica]

not to text, It needs to be in numeric without the colon or the M. Thanks

[63falcondude]

Format: Custom > hmma/p
Continues to see cell as an actual time.

Convert: =TEXT(FLOOR(A1,1/24),"hmma/p")
Cell will now be seen as Text.

It sounds like you want to format the cell. Not convert it.
I provided both just FYI.

[AliGW]

Custom time format: hhmma/p

Excel 2016 (Windows) 32 bit

	C	D
4	11:45 AM	1145a

Sheet: Sheet1

[José Augusto]

Try to format with

hmmA/P

[63falcondude]

Ali, hh will give military time.

[AliGW]

The OP may want this - it's not clear from their requirements.

[anjelica]

Thank you! It was so simple! I was trying to make it complicated :D

[AliGW]

Happy to help!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FDibbins]

welcome to the forum

For future reference, please take a moment to read the forum rules and use thread titles that are descriptive of your problem - not what you think the answer might be. (think google search terms?).

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?