Excel - Shift Time By N Hours In Excel - Episode 1387
1,071 views · Published 20 April 2011 · 2:47 · Indexed 10 November 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial: How to Add or Subtract Hours from Time Fields in Excel. Welcome back to the MrExcel netcast! In this episode, we're tackling a question sent in by Bill about working with date and time fields in Excel. Bill has a series of data that all have a time of midnight, but he wants to add the real time to these fields. Specifically, he wants to move them all to 6 pm. But how do we do that in Excel? Well, the first thing to understand is how Excel stores time. It's actually a fractional portion of the day, with noon being 0.5 and 6 pm being 0.75 (since it's 75% of the day). So my suggestion is to start by entering 0.75 in a blank cell. Then, we can use the "Paste Special" function to quickly update all the desired cells. To do this, select the cells you want to update (you may need to hold down the control key to select multiple cells). Then, use the shortcut Alt+E+S to bring up the "Paste Special" menu. In this menu, we want to choose "Values" from the top and "Add" from the Operation section. This will take the value on the clipboard (0.75) and add it to the existing cells, effectively moving them to 6 pm. But what if we make a mistake and want to go back to 3 pm? No problem! We can use the same process, but this time we'll enter +3/24 in the blank cell (since 3 pm is 0.125 of the day). Then, we'll use the "Paste Special" function again, but this time we'll choose "Subtract" from the Operation section. This will subtract 0.125 from the existing cells, effectively moving them back to 3 pm. Once you get the hang of this process, you can easily slosh back and forth between different times by adding or subtracting the appropriate decimal proportion. Thanks to Bill for sending in this question and thank you for tuning in to another netcast from MrExcel. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents (00:00) Add 18 Hours to all Excel Cells (00:32) How Excel stores time (01:30) Copy 0.75 and Paste Special Add to all Times (02:00) Subtract 3 hours from all times (02:26) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #exceltime This video answers these common search terms: excel how to add an hour to a time how to add minutes or hours to an excel time excel how to add 18 hours to each time cell excel add hours to time shift time by N hours in excel excel formula to add hours to time how to add hours to a time and date in excel how to add more hours in time excel adding a time interval in excel time calculations with 18-hour offset in excel increase time values by 18 hours in excel adjust time cells by N hours in excel excel time addition with N-hour increment shift time forward by 18 hours in excel adding 18 hours to each time entry in excel Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153066/ 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.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920