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

Watch on YouTube

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