01/01/1900 and Time Formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Excel 2003.

I added 4 hours to time which works perfectly. When I want to get rid of
the formula, I use Copy-->Paste Special-->Values. The time displays
correctly in the cell but in the formula bar it shows 01/01/1900 along with
the time. When I format the cell as again to time hh:mm:ss, nothing changes
as I still see the date in the formula bar.

How do I stop this without have to go into each cell individually and delete
the date out?

Too crazy for me, what gives?
 
Are you using the TIME function to add hours like if A2 holds the original
time

=A2+TIME(4,0,0)

You can instead put the hours you are adding in another cell (for example
B2) and then use

=A2+(B2/24)

or hard coded

=A2+(4/24)

format as time, copy and paste special




--
Regards,

Peo Sjoblom

(No private emails please)
 
I put the hours into another cell and added them as suggested. After that, I
format the cell to time, hh:mm:ss, Still, both date and time show up in the
formula bar. I want to eliminate the date in the forumla bar
 
A new discovery! The date doesn't copy over as long as you don't go past
midnight. When you paste special-->values; the time only shows in the
formula bar provided it's within the same day. If the time goes past
midnight, 01/01/1900 goes into the formula bar along with the time.

Bugs in programming for sure! Way to go Microsoft.
 
I don't think that is a bug per see, just a stupid thing that excels does
with dates and times, Excel adds one day to the hours and applies a date
time format, it happens if you put 26:00 in a cell as well. Since Excel's
date system (in windows) starts with January 0 1900 it adds one day to that
thus the 1/1 1900. To override this you can use a text value, assume you
want to add times in A1 and B1

=TEXT(A1+B1,"[hh]:mm")

copy and paste special

or you can hide the formula bar



--
Regards,

Peo Sjoblom

(No private emails please)
 
Back
Top