Excel In Your Office: Custom Time Format

We saw how to build a custom date format in our earlier post. Now let’s see how to build custom time formats. Remember the coding table? I gave extract of time related codes here.

Let’s see how to code a custom time format.

Display as 11:15:10 AM

We need code for

A 2 digit hour
2 digit minute
2 digit second
AM or PM

So our code would be

hh:mm:ss AM/PM

Display Only Hours or Minutes or Seconds

You will just have either hh or mm or ss in the code as per your need.

But the problem in the above format is that you will see only hours equal to or less than 23, minutes equal to or less than 59 and seconds equal to or less than 59. Once the seconds cross 59, it will become 0 and minutes will be increased by 1. When minutes count crosses 59, it will become 0 and hours will be increased by 1. Similarly, when hours cross 23, it will become 0 and days count will be increased by 1.

If you just want to see the total hours (like 38 hours), total minutes (like 75 minutes) or total seconds (like 145 seconds) between two date/time values: we shall enclose the respective character in closed brackets. For example, if you want to see how many hours elapsed between two date / time values, below will be the code.

[hh]:mm:ss

A comparison between different formats is given below. In third row, I am trying to subtract second date from first date.

Note that in third column, since hours will not be added to days count even when it crosses 24 hours, you should not have code to show days count. If you input day’s representative code, [hh] will be considered as hh only i.e. hours more than 24 will be added to days count.

Accordingly, if you enclose minute’s code in brackets, you should not have hour’s code; if you enclose second’s code in brackets, you should not have hour’s and minute’s code.

In similar fashion, you can arrive at number of minutes or number of seconds elapsed by enclosing those codes in brackets.

Explore other combinations you require. If you are not able to make up the one you want, just let me know. Happy Learning!!