LinkBack URL
About LinkBacks
Hi,
I have a sheet that automatically works out the number of hours assigned to a job form a list of many items, i want the total to automatically round to the nearest 4 hours.
All help is much appreciated.
Ben
Hi,
I have a sheet that automatically works out the number of hours assigned to a job form a list of many items, i want the total to automatically round to the nearest 4 hours.
All help is much appreciated.
Ben
if its real times eg 31:00:00
then maybe
=MROUND(A1,4/24)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
welcome to the forum, Ben. say your hours are in A1, try:
=MROUND(A1,"4:00")
or replace A1 with the existing formula that works out the number of hours. you also need to format the cells as:
[h]:mm
this is so that you can see more than 24 hours
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
May be this…
In A1 Cell
10:23 AM
In B1 Cell
=TIME(FLOOR(HOUR(A1),4),0,0)
Or
=TIME(CEILING(HOUR(A1),4),0,0)
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Thanks for the responses, the numbers are not in a time format, they are just a total amount of hours, so A1 =21, this is 21 hours, but i want it rounded up to the nearest whole number divisible by 4, so in this example it would be 24. If A1= 11 it would be 12.
=ceiling(a1,4)
=CEILING(A1,4) then
Thanks all, i have not heard of ceiling before, i will use that time and time again.
=CEILING(A1,4/24)
format as time
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks