Log In
Register
Home
New Member
August 15, 2019
Offline
I have a excel time sheet that I have to enter my time in everyday. I would like it to return a value if I put in 7:16 to 7:29 or 7:31 to 7:44 to round it to 7:30 for morning, and 3:46 to 3:59 or 4:01 to 4:14 to round it to 4:00 pm in the afternoon. I have attached my excel sheet for reference.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
You can use MROUND function to round up or down to nearest half hour.
Example: The entered time of 7:12 in cell C3 will with below formula be shown as 7:00.
=MROUND(C3,30/60/24)
Just wrap it with needed functions if you want this rounding only to occur within specific time values.
New Member
August 15, 2019
Offline
I just need the times as stated above "I would like it to return a value if I put in 7:16 to 7:29 or 7:31 to 7:44 to round it to 7:30 for morning, and 3:46 to 3:59 or 4:01 to 4:14 to round it to 4:00 pm in the afternoon"
So, how would I use the above formula "=MROUND(C3,30/60/24)" to make it work?
VIP
Trusted Members
December 7, 2016
Offline
Hello,
My apologies for not giving you a complete formula in my previous answer. As I do not know how you want this to work, for example where you want the rounded times to show, if you want to be able to change the time criterias without changing the formula or not, I left my previous answer incomplete.
=IF(OR(AND(C3>$B$16,C3<$B$17),AND(C3>$B$18,C3<$B$19)),MROUND(C3,30/60/24),C3)
The formula above is a complete formula. In this example I have entered the time criterias in cells B16 to B19, as listed below.
B16 = 07:15 | B17 = 07:45 | B18 = 15:45 | B19 = 16:15
By listing the criterias like this I am able to change without interfering with the formulas. I hope this answer gives you help to continue with your work.
1 Guest(s)
