In some cases, it may be necessary to advance or decrease the time in the unit determined by the company, for example, in attendance management. In this issue, we will introduce a method to use the entered time in fixed time increments.
The methods using the CEILING and FLOOR functions areHow to display time in 15-minute increments (CEILING and FLOOR functions) ExcelPlease also refer to the following information.
How to use the "TIME" function
TIME(hour,minute,second)
First of all, we must find the "B3To display the time information entered in the "C3" cell in the "C3" cell, enter the following in the "C3" cell. Since "seconds" is not relevant this time, we will start from the beginning.0to the
=TIME(HOUR(B3),MINUTE(B3),0)
When executed, the same time and same minute as the "B3" cell are now displayed in the "C3" cell, as shown in the figure below.
The next method is to carry forward and carry back. The idea is to divide the target time by the delimited time.
If it's 14 minutes, then ......14÷15=0.933...
For 15 minutes, go to ......15÷15=1
If it's 16 minutes, then ......16÷15=1.066...
and the result will be a decimal if the time is not in 15-minute increments. Using this property, the decimal point in the result of this calculation can be carried forward or backward to express 15-minute increments.
Time Advancement Method
15Minute by minute, carried forwardmaking a tuckEnter the following for
=TIME(HOUR(B3),ROUNDup(MINUTE(B3)/15,0)*15,0)
As a result of entering this function, the 29th minute was moved up to the 30th minute, as shown in the figure below.
Let's reflect this function to the cells below.
Then, as shown in the figure below, you will see the results moved up in 15-minute increments.
Time Deferment Method
15Minute by minute, carried forwardloweringEnter the following for
=TIME(HOUR(B3),ROUNDDOWN(MINUTE(B3)/15,0)*15,0)
As a result of inputting this function, the 29th minute is moved down to the 15th minute, as shown in the figure below.
Let's reflect this function to the cells below.
Then you will see the results displayed in 15-minute increments, as shown in the figure below.
For 30-minute increments
In the above, the function was created in 15-minute increments, but if you want to create functions in 30-minute increments, use the following15part of the30to change it to 30-minute increments.
=TIME(HOUR(B3),ROUNDDOWN(MINUTE(B3)/)15,0)*15,0)↓arrow (mark or symbol)
=TIME(HOUR(B3),ROUNDDOWN(MINUTE(B3)/)30,0)*30,0)
Conclusion
As we have seen, by changing the division variable, you can freely set 3-minute, 5-minute, 10-minute, etc. increments.
The methods using the CEILING and FLOOR functions areHow to display time in 15-minute increments (CEILING and FLOOR functions) ExcelPlease also refer to the following information.
I hope the article I have presented here will be of help to you.
Comment