One more question re: pay calcs using [h]:mm format for time worke

[Guest]

When I format a cell as [h]:mm to represent hours and minutes worked, say,
26:30 for 26 and 1/2 hours, it shows in cell as 26:30, but shows in the equal
bar (don't know what it's called) above the spreadsheet as "1/1/1900
2:30:00AM" Why and is there a way to have that equal bar show the same as
what's in the cell, i.e. 26:30? The pay calculations work fine; it's just
disconcerting to click on a cell for time worked ( i.e. 26:30) and see what
appears to be gibberish (i.e. 1/1/1900 2:30:00AM) in the equal bar above the
spreadsheet.

 

[Guest]

Also, when cell is formatted as [h]:mm it appears in formula bar as time of
day, i.e. 9:30 for 9 and 1/2 hours shows as 9:30:00 AM. How do I get formula
bar to show it simply as number of hours and minutes as entered in cell and
not as time of day? Thanks.

 

[T. Valko]

in the equal bar (don't know what it's called)

It's called the formula bar.

The reason it displays like that in the formula bar is because:

26:30 = 1 day + 2 hrs and 30 mins

Excel is "eager" to display dates. So, it interprets 1 day + 2 hrs and 30
mins as 1/1/1900 2:30:00 AM.

There is nothing you can do about that. You could hide the formula bar
altogether but it's probably not what you really want to do:

Goto View>Formula bar

 

[Guest]

Thanks! I thought there might be a way to enter time simply as hours and
minutes without the hiergolyphics in the background (formula bar). Anyway,
the calculations work; that's what matters I guess, not the aesthetics. File
this under anal retentive or obsessive compulsive. Thanks again!

in the equal bar (don't know what it's called)

It's called the formula bar.

The reason it displays like that in the formula bar is because:

26:30 = 1 day + 2 hrs and 30 mins

Excel is "eager" to display dates. So, it interprets 1 day + 2 hrs and 30
mins as 1/1/1900 2:30:00 AM.

There is nothing you can do about that. You could hide the formula bar
altogether but it's probably not what you really want to do:

Goto View>Formula bar

--
Biff
Microsoft Excel MVP

When I format a cell as [h]:mm to represent hours and minutes worked, say,
26:30 for 26 and 1/2 hours, it shows in cell as 26:30, but shows in the
equal
bar (don't know what it's called) above the spreadsheet as "1/1/1900
2:30:00AM" Why and is there a way to have that equal bar show the same as
what's in the cell, i.e. 26:30? The pay calculations work fine; it's just
disconcerting to click on a cell for time worked ( i.e. 26:30) and see
what
appears to be gibberish (i.e. 1/1/1900 2:30:00AM) in the equal bar above
the
spreadsheet.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks! I thought there might be a way to enter time simply as hours and
minutes without the hiergolyphics in the background (formula bar). Anyway,
the calculations work; that's what matters I guess, not the aesthetics.
File
this under anal retentive or obsessive compulsive. Thanks again!

in the equal bar (don't know what it's called)

It's called the formula bar.

The reason it displays like that in the formula bar is because:

26:30 = 1 day + 2 hrs and 30 mins

Excel is "eager" to display dates. So, it interprets 1 day + 2 hrs and 30
mins as 1/1/1900 2:30:00 AM.

There is nothing you can do about that. You could hide the formula bar
altogether but it's probably not what you really want to do:

Goto View>Formula bar

--
Biff
Microsoft Excel MVP

When I format a cell as [h]:mm to represent hours and minutes worked,
say,
26:30 for 26 and 1/2 hours, it shows in cell as 26:30, but shows in the
equal
bar (don't know what it's called) above the spreadsheet as "1/1/1900
2:30:00AM" Why and is there a way to have that equal bar show the same
as
what's in the cell, i.e. 26:30? The pay calculations work fine; it's
just
disconcerting to click on a cell for time worked ( i.e. 26:30) and see
what
appears to be gibberish (i.e. 1/1/1900 2:30:00AM) in the equal bar
above
the
spreadsheet.