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.
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.
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?
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.
