I HAVE TIME SHEET IN EXCEL 2002
A1 = 07:00 AM
B1 = 11:16 AM
C1 = 04:16 (A1-B1)
How can i round 04.16 in 30 minutes interval.
Thanks
Skaran
I HAVE TIME SHEET IN EXCEL 2002
A1 = 07:00 AM
B1 = 11:16 AM
C1 = 04:16 (A1-B1)
How can i round 04.16 in 30 minutes interval.
Thanks
Skaran
Last edited by kumawat_s; 10-04-2013 at 09:07 AM.
Try "=(ROUNDUP((B1-A1)*24*2,0))/(24*2)"
Please try this file.
Please click 'Add reputation', if my answer helped you.
=mround(b1-a1,"0:30")
"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
MROUND rounds to the closed Fix. With Timesheets, I would expect a roundup to be more common, but please use whichever suits your requirements.
Thanks. It is working in Excel XP.
Could you please eloborate the function (first we multiplied 24*2 and then divided)
Hi
Time in Excel is shown as a fraction of a Day. My multiplying by 24, I now have the Units as hours. Take any number, double it and round it up to zero decimal places and you will get an odd or even result. Divide that by 2 again and you will have a number that is either x.0 or x.5. Divide that again by 24 and you have got back to how Excel stores time values.
Run it on some test data, stage by stage.
Stephen
Just in case you ever need it, if you did "=(ROUNDUP((B1-A1)*24*4,0))/(24*4)" you would then be rounding to the next 15minute point on the clock.
=CEILING(b1-A1,"00:30") to round up
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks