• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time formatting

Shayeebur

Shayeebur

Member
whenever i enter time into excel like 5:15 it automatically shows am or pm in formula bar which i dont want, i even formatted it like [hh]:mm but still it showing me am or pm in formula bar with time like 5:15 am so it should be seen like 5:15 in formula bar pls advice any formatting for this.
 
Good day Shayeebur


I have entered some times as you wish to display them with no problems, double checked in custom in the number tab and they where formatted as hh:mm, no square brackets.
 
hi

after formatting hh:mm i can still watch 5:15 am in the formula bar which i dont want to see, i want to see only 5:15. pls help
 
The formula bar will display the actual value/formula, while the cell will display the text that is printed. A time of 5:15 is the same as 5:15 AM which is the same as 0.21875

Another example is the time 27:45. This is the same as 1/1/1900 3:45 AM, which is the same as 1.15625

The true value stays the same, the only difference is how XL interprets it. A question I would have is, "why does it matter?"


But, possible ideas:

1. Hide the formula bar

2. input the time as '5:15 The single apostrophe will cause XL to take what you type literally, and not convert it. Downside is that it is now a text string rather than a number.
 
Good day Shayeebur


I have reentered data again and I can not get am/pm when it is custom formatted as hh:mm, even formatted as time it does not display am/pm.


If I enter 5:30 what is displayed is 05:30(custom hh:mm) and the formula bar displays 05:30:00. I do not know where you are getting the am/pm from.


If formatted as straight forward time when 5:30 is entered it is displayed as 05:30:00

still no am/pm.!!!!


I think you need to check your cell formatting.
 
@bobhc


With a cell formatted first as General, if you input "5:30", the formula bar will read:

5:30:00 AM

Changing the cell formatting has minimal effect on the formula bar, the only exceptions are when you change category (Number/Date/Percentage)
 
Good day Luke


I was assuming that the OP was not using General as formatting for the cells, OP did say that they had formatted as hh:mm and was still getting am/pm in the formula bar, as I was trying to show, no matter what formatting I used (other than general) hh:mm or time am/pm does not show.


Do not understand why the show in the formula bar is of such concern!!
 
@bobhc

Yes, you are correct. I was simply using the General as a starting point to make sure it wasn't Number/Percent/Text.
 
Hi Shayeebur ,


Go into Control Panel , Regional and Language Settings , Customize , Time :


If you see hh:mm:ss tt


remove the tt.


Narayan
 
+1 for Narayan. that explains also why bobhc and I were seeing different things. =P
 
You must log in or register to reply here.
Back
Top