Today, in Episode #1387, Bill H. asks, "How do I change a date/time field from midnight to 6PM?" Excel stores time values as a Fractional portion of a day [where a day is = to 1], so MrExcel shows us how to change our time values using Paste Special.
Transcript of the video:
MrExccel podcast is sponsored by Easy-XL Learn excel from MrExcel Podcast. Episode 1387. It's 6 o' clock somewhere Well, hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in by Bill. Now I know Bill.
Bill has a whole series of date and time fields.
and see they're all at midnight in other words there's really no time there at all.
He wants to go through and add the real time and in this particular case he wants to move things up to 6 pm.
All right now 6 pm. The way that excels stores times.
You know it's a fractional portion of the day, so noon is 0.5 6 pm is 0.75 because its 75% percent of the day is over and so my suggestion is just to go out to a blank ell.
Enter 0.75 in that cell and then we're going to copy that cell onto the clipboard and then choose the items that you want to update you know maybe you have to hold on the control key because it's not everything and then we want to do a "Paste Special" all right now "Paste Special" has been moving around.
It was "Edit" "Paste Special" back in excel 2003.
In 2007 we had the Paste drop-down with "Paste Special" at the bottom 2010 "Paste special" at the bottom yeah, I no matter what version, I always use the Alt+E+S.
So hold on Alt+E or just press Alt+E you get the office access key up here then press S alright And then I want to choose two things in here. We want to choose Values from the top, so that's V we also want to choose right here Add from the Operation so in other words we're taking what's on the clipboard the 0.75 and adding it to the existing cells.
The reason you have to choose Values is if you didn't, it would take the Format and apply that as well which would change all those Date and Time formats into decimals and it would judt look ugly, so we click OK and you see that now all those are automatically 6pm, what if we decide that that's wrong, and now we want to go back to 3 pm.
Now that we're already at 6 pm well, so 3 Let's just do it + 3/24 that is that portion of a day you are going to find thats too easy.
Change that to values. Now we copy that from the clipboard and choose these cells.
Alt+E+S for "Paste Special" "Values" and this time subtract Click ok and we're back to 3 pm. So see once you get the hang of this you can just slosh things back and forth by your adding or subtracting a decimal proportion.
Hey, I want to thank Bill for sending that question in.
I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Today's question sent in by Bill. Now I know Bill.
Bill has a whole series of date and time fields.
and see they're all at midnight in other words there's really no time there at all.
He wants to go through and add the real time and in this particular case he wants to move things up to 6 pm.
All right now 6 pm. The way that excels stores times.
You know it's a fractional portion of the day, so noon is 0.5 6 pm is 0.75 because its 75% percent of the day is over and so my suggestion is just to go out to a blank ell.
Enter 0.75 in that cell and then we're going to copy that cell onto the clipboard and then choose the items that you want to update you know maybe you have to hold on the control key because it's not everything and then we want to do a "Paste Special" all right now "Paste Special" has been moving around.
It was "Edit" "Paste Special" back in excel 2003.
In 2007 we had the Paste drop-down with "Paste Special" at the bottom 2010 "Paste special" at the bottom yeah, I no matter what version, I always use the Alt+E+S.
So hold on Alt+E or just press Alt+E you get the office access key up here then press S alright And then I want to choose two things in here. We want to choose Values from the top, so that's V we also want to choose right here Add from the Operation so in other words we're taking what's on the clipboard the 0.75 and adding it to the existing cells.
The reason you have to choose Values is if you didn't, it would take the Format and apply that as well which would change all those Date and Time formats into decimals and it would judt look ugly, so we click OK and you see that now all those are automatically 6pm, what if we decide that that's wrong, and now we want to go back to 3 pm.
Now that we're already at 6 pm well, so 3 Let's just do it + 3/24 that is that portion of a day you are going to find thats too easy.
Change that to values. Now we copy that from the clipboard and choose these cells.
Alt+E+S for "Paste Special" "Values" and this time subtract Click ok and we're back to 3 pm. So see once you get the hang of this you can just slosh things back and forth by your adding or subtracting a decimal proportion.
Hey, I want to thank Bill for sending that question in.
I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
