Amongst the various format that number takes, time is the one of the most commonly used formats, after currency and accounting number formats. We need to record time for various purposes – for instance to mark the in and out timing of staff, for arrival or dispatch of goods, to record time lapse between two events and so on.
In today’s post we will learn how to enter time correctly, how to make it take various time formats and how to perform basic operation of adding and subtracting time.
Let’s start with the very basic things – how time is recorded in Excel.
We can format number as time by pointing to Tab Home > Number and selecting Time to format number as time, the result is this:
If you select the custom number format you will find number formatted as following:
In this format h represents hours, mm represents minutes and ss seconds and AM/PM have usual meanings.
Right clicking and selecting the second option in the Number format dialogue will set number to 24 hour format:
The under laying format is:
Thus if we enter 4.30 pm in a cell, it will appear as:
We can still show seconds for the last 24 hours format by adding ss to it. The new format will be: h:mm:ss;@
Adding numbers formatted as TIME:
Let’s say we want to add to time durations – we want to 04:30:00 to 02:25:00 how should we proceed:
We simply added the two number using the formula A3=A1+A2, the result initially was shown as 24 hour a day format – we added square brackets around the hours section of the number to make it go beyond 24 hours.
You can subtract as we subtract usual numbers, but since time cannot assume negative values, subtracting larger value from a smaller will return an error. In order to check for this you can add an IF () construction. If we do it manually for the already presented example the result comes out to be:
All of the formats described in the above post can be accessed and applied using TEXT