Home |
Search |
Today's Posts |
#1
|
|||
|
|||
01/01/1900 and Time Formatting
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? |
#2
|
|||
|
|||
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) "AndreaW" wrote in message ... 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? |
#3
|
|||
|
|||
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 "Peo Sjoblom" wrote: 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) "AndreaW" wrote in message ... 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? |
#4
|
|||
|
|||
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. "AndreaW" wrote: 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 "Peo Sjoblom" wrote: 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) "AndreaW" wrote in message ... 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? |
#5
|
|||
|
|||
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) "AndreaW" wrote in message ... 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. "AndreaW" wrote: 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 "Peo Sjoblom" wrote: 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) "AndreaW" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formatting | Excel Discussion (Misc queries) | |||
Date and time formatting | Excel Discussion (Misc queries) | |||
adding time zones to formatting | Excel Worksheet Functions | |||
Time & Date Formatting | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |