Formula Bar - display of content

[Nadine]

Have cells custom formatted for seconds :ss
but in the formala bar shows and invalid date and time, is there a way to
make the formula bar show the correct value of the cell

i.e.
typed in cell - 55 for 55 seconds
but the formula bar showing content as: 1900-02-24 12:00:00 AM
for me to get it to display as :55
have to enter it as :55
but then can't use it for a calculation

if format it as mm:ss
enter 55 shows as 00:00 in the cell
but in the formula bar shows content as: 1900-02-24 12:00:00 AM

need cells to display a seconds :55
but will need to use the cells for other calculations

 

[David Biddulph]

You need to enter it as 0:0:55

 

[Jim Thomlinson]

Entering time is a bit of a beast. If you just enter in 55 what you are
actually entering 55 days after January 1 1900. That is beause of how dates
and times are store in XL. Dates & times are sotred as decimal values. Teh
integer portion is the number of days that have transpired since Jan 1, 1900.
The decimal part it the fraction of 24 hours that have transpired. So for
example 0.25 is 6:00 am and 0.5 is noon.

There is no inherant way to directly enter times as integers. You need to
use the colon which tells XL that what is being entered is a time and XL then
does the conversion to a decimal.

A couple of options.

1. Enter 55 in a cell. In another cell divide 55 by 86,400 (seconds in a
day) and you will get 0.000637 which when formatted as time shows up as 55
seconds.

2. Check out this link on a macro solution.
http://www.cpearson.com/excel/DateTimeEntry.htm

That site also has an excellent description of how dates and times work...

 

[Gord Dibben]

In line with what Jim T posted you can add this sheet event code to your
worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'change a number to minutes/seconds...4 = 00:00:04 ....91 = 00:1:31
Const WS_RANGE As String = "A1:A10" 'edit to suit
If Target.Count > 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = .Value / 86400
.NumberFormat = "[hh]:mm:ss"
Application.EnableEvents = True
End With
End If
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the above code
into that module.

Edit to suit your range.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

 

[Nadine]

Entering it as - 0:0:55 works to display it as seconds
but the formula bar shows - 12:00:55 AM

 

[Nadine]

that macros a bit beyond my understanding.

Will just live with the 0:0:55 option

Thanks

 

[David Biddulph]

It doesn't matter what it says in the formula bar; you can format the cell
as [h]:mm:ss
If you want the formula bar to display in 24 hour format, rather than 12
hour, you might try changing your Windows Regional Options (in Control
Panel, not in Excel).