Formulas

How to Make use of Time Format in Excel

Pinterest LinkedIn Tumblr

Ever tried using time formats in Excel?
Everything in this world moves around time, and Excel is pretty smart in storing time and having a flexible format with the device. In each data, time could be formatted with various codes and with customized time formats. In this article, we’ll be looking at the inputs relevant for storing and formatting time in Excel.

Introduction

In Excel, time can be formatted using different formats and functions. By using time formats that are available in Excel, the user could change the default time as well as can measure time intervals from the provided data. The various time formats and custom time formats that are available in Excel can be discovered by referring to the illustrations and descriptions provided in this article. This article includes relevant information about the built-in and custom time formats in Excel.

Synopsis

In this article, the user could gain a fair knowledge about making amends with the time provided in the data. The scenarios are built for demonstrating the formats that can be used in data. It includes formats such as:
1. Default time formats.
2.Basic time formats and
3.Custom time formats.

Default time formats

At the bottom, the time format differs from region to region. The user could get the exact time of a particular region by having an eye on the regional time settings. This can be done by setting the regional time format with the PC device. This requires certain techniques to be followed:

  • By changing the regional time settings in the PC device, the user could extract the time with different time codes based on the regional time settings that are set to the device.
    To know more about regional time settings, click here.
  • The user could extract time based on different codes as shown in the image.
How to Make use of Time Format in Excel 1
  • Further, time can be extracted and could be represented as shown in the image. This requires a few steps to be applied to retrieve the desired result.
    Step-1: While time is entered in a format ‘hh: mm’, Excel shows the real-time format into a fraction, when the user changes it to a general format because it has been divided as ’10hrs/24hrs’ as shown in the image.
How to Make use of Time Format in Excel 2

Step-2: The time can be represented using a ‘Text’ formula as it provides the time with real-time format instead of providing a fraction as result. The time can be represented as per the codes by applying the formula
=TEXT(value, format_text).
The arguments can be taken as the value and code in which it has to be formatted.

How to Make use of Time Format in Excel 3

Step-3: This has to be followed for all the codes that give the required result as shown in the image.

How to Make use of Time Format in Excel 4

Basic Time Formats

In Excel, time can be formatted with built-in or custom formats. Yet, the user can also calculate intervals and time between hours active and hours inactive. Excel provides the relevant format to measure the number of hours between the start and the end of the day either for official purposes or for personal usage. Now let’s looking into the steps involved in getting the total hours from when the day begins and when the day ends.

How to Make use of Time Format in Excel 5

Step-1: From the provided data, the user should convert the time-in and time-out by selecting the columns and by pointing to the ‘Number’ groups.

How to Make use of Time Format in Excel 6


Step-2: In the drop-down menu, the user should select ‘more number formats’ to format the time as per requirements.

How to Make use of Time Format in Excel 7

Step-3: When the ‘Format cells’ dialogue box opens, the user should select the preferred format and should select the location from the drop-down menu.

How to Make use of Time Format in Excel 8



Step-4: The time gets formatted with the chosen format as shown in the image.

How to Make use of Time Format in Excel 9

Custom Time Format

If the user wants to seek the time between the start and the end o the day, it can be done by changing the time provided in the data to a standard format. Even the user can customize the default format to a new one. Now let’s see the inputs to build their own customized format.
As given the image below, the ‘break-time’ column has been made only with the number of minutes instead of putting it with a proper time format.

How to Make use of Time Format in Excel 10


This can be sorted out by changing the number format to a standard time format.
To use a customized time format, the following inputs have to be followed:
Step-1: By selecting the ‘break-time’ column, the user must get to the ‘format cells’ dialogue box using the shortcut ‘ctrl+1’.

How to Make use of Time Format in Excel 11

Step-2: By selecting the ‘custom’ option under ‘category’, the user must type the preferred format ‘hh:mm:ss’ in the type box. And then click OK.

How to Make use of Time Format in Excel 12

Now the ‘break-time’ column gets formatted to the standard time format as shown in the image.

How to Make use of Time Format in Excel 13

Scenario- To calculate the hours worked by an employee.

The user can measure the number of working hours using the ‘Text’ formula by infusing certain techniques as given below:

  • Firstly, the user must calculate the hours worked by using the formula.
    =Time out-Time in-Break time.

How to Make use of Time Format in Excel 14
  • This has to be followed to get the ‘hours worked’ as shown in the image.
How to Make use of Time Format in Excel 15
  • Now the total working hours can be retrieved by using the formula
    =SUM(cells in hours worked)
How to Make use of Time Format in Excel 16
  • The total working hours will be extracted as shown in the image.
How to Make use of Time Format in Excel 17

Hope this article contains the relevant output to format time in Excel.

For more details, visit our youtube channel.

Related Posts

Write A Comment