MS Excel: TIME() Function
Adjusting the time in Microsoft Excel is quite easily done and the TIME() function has a number of hooks that can be utilised to your advantage.
The date() function works by all parts the same
Understanding the way Excel uses TIME()
The formula for referencing the time set in A1 is as follows
TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
Putting the above formula in cell B1 will give you the same time as set in A1.
Excel treats time as 3 separate pieces of data, these being Hour, Minute and Second.
You can set a particular element of time to whatever value you’d like. For example, If you have a time of 14:34:00 and only want use the hour of the day in A1 you would use
TIME(HOUR(A1),0,0)
This is telling Excel to get the Hour from A1, but set the minute and second to 0 resulting in 14:00:00
Furthermore,
TIME(HOUR(A1),10,10)
will result in
14:10:10
Using Arithmetic in =TIME()
Knowing how the =TIME() function works, you can now perform arithmetic on the time.
Adding 3 hours to the time in A1 is simple by adding a +3 to the HOUR reference
TIME(HOUR(A1)+3, MINUTE(A1), SECOND(A1))
Subtracting 3 hours to the time in A1 is simple by adding a -3 to the HOUR reference
TIME(HOUR(A1)-3, MINUTE(A1), SECOND(A1))
Adding 3 hours to the time in A1 and rounding down to the hour is just by assigning 0 to the minute and second reference.
TIME(HOUR(A1)+3,1,1)
Getting Fancy
Rounding time off to the nearest hour: Using a simple ‘if else statement’ we can refer to the time in A1 and see if the minutes and seconds are greater than or equal to 30:00 and then round up or round down as required.
IF((TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
