HomeApplication

How to round time to 30 minutes or 15 minutes in Excel

With time statistics such as working time, overtime, we will have a rounding function to bring the rounded time value to half an hour or 15 minutes. Such a rounding will be useful in you summarizing time, calculating more favorable time. The following article will guide readers to use the MOD function to round time to 30 minutes and MOD to round time to 30 minutes.

1. How to use the MOD function to round time to about half an hour

At the time table to be rounded, in the first time input box enter the formula = MOD (INT (B2 / (1/48)) * (1/48), 1) where B2 is the value cell time wants to be rounded.

How to round time to 30 minutes or 15 minutes in Excel Picture 1

Output the decimal number as shown below. Next you need to change the format for this cell to Time is okay. You click on the results box and then in the Number group on the toolbar select the format as Time as shown.

How to round time to 30 minutes or 15 minutes in Excel Picture 2

Now the cell has been converted to the time format and rounded to half an hour ago as shown. Now we just need to drag the first cell result down to the remaining cells.

How to round time to 30 minutes or 15 minutes in Excel Picture 3

2. How to use the FLOOR function rounds time to about 15 minutes

To round the time back to 15 minutes ago, we will use the FLOOR function. In the first cell enter the result of rounding time to 15 minutes, enter the formula = FLOOR (B2; TIME (0; 15; 0)) . Note that if you want to round to another value, then enter that time in the formula is okay.

How to round time to 30 minutes or 15 minutes in Excel Picture 4

If the result shows a decimal, you also need to change the format to Time.

How to round time to 30 minutes or 15 minutes in Excel Picture 5

If you want, you can also round the time back to 20 minutes ago with the same formula.

How to round time to 30 minutes or 15 minutes in Excel Picture 6

And you also get the result of the time rounding.

How to round time to 30 minutes or 15 minutes in Excel Picture 7

4 ★ | 6 Vote
Excel time functionround function in excel

You should read it

May be interested

  • How to change auto save time on Excel, Auto Save Excel FileHow to change auto save time on Excel, Auto Save Excel File
    auto save in excel is a useful feature that automatically saves your files while working, minimizing the risk of data loss due to unexpected incidents such as power outages or automatic shutdowns or resets. you can adjust the settings and change the auto-save time on excel after a certain period of time, depending on the user's needs.
  • Instructions for searching and replacing in Excel tablesInstructions for searching and replacing in Excel tables
    with excel spreadsheets containing hundreds and thousands of data, search and replace by formula will help us save a lot of time, increase work efficiency compared to manual methods.
  • Why 1 minute has 60 seconds, 1 hour has 60 minutes and 1 day has 24 hours?Why 1 minute has 60 seconds, 1 hour has 60 minutes and 1 day has 24 hours?
    thanks to the ancient civilizations that defined and preserved the rules of dividing time, modern society has the laws of 1 day with 24 hours, 1 hour has 60 minutes and 1 minute has 60 seconds.
  • Date time functions in ExcelDate time functions in Excel
    excel supports you to process and calculate quickly with the functions that excel provides such as calculation functions, date functions ... one of them is the function of time to help you handle the prices. time value: hour, minute, second conveniently
  • 5 source to get macro to automate Excel spreadsheets5 source to get macro to automate Excel spreadsheets
    macros can help eliminate simple tasks from the workflow and allow users to take time to focus on more important issues.
  • How to use Name Box in ExcelHow to use Name Box in Excel
    using name box saves you a lot of time when working in excel and that is why you should not ignore this utility.
  • Instructions on how to copy formulas in ExcelInstructions on how to copy formulas in Excel
    when we work with calculations on excel with many different data, copying formulas from one column to another will save time much more work.
  • How to open 2 Excel files at the same time on the screenHow to open 2 Excel files at the same time on the screen
    in the process of working with excel, sometimes you need to process 2 or more excel files at the same time, but the version of excel you are using cannot open. so you are looking for ways to open 2 excel files at the same time on the screen, so let's find out in tipsmake.com in the article below.
  • Forgot password protected Excel file, what should you do?Forgot password protected Excel file, what should you do?
    suppose in case if there is an excel file containing extremely important data and because you want to keep your data secret, you create a password for the excel file to ensure its safety and data protection. file. however, for a long time not to use, forgetting passwords is also common.
  • Quickly insert current date and time into a cell in ExcelQuickly insert current date and time into a cell in Excel
    in excel, you can quickly fill out current date, month, and time information as static or dynamic values. the static value is the date and time value at the time of filling without automatically updating on subsequent days when you open or perform calculations on a spreadsheet.

Application