Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

Question

Another Timesheet with a Twist

Dec 8, 2014 9:18PM PST

I need the formula to round up or down based on 7min and 50 secs.
So 8:07:50 rounds up to 8:15:00
and 8:07:49 rounds down to 8:00:00

Can you Help?

Discussion is locked

- Collapse -
Answer
R: formula
Dec 8, 2014 10:05PM PST

I can imagine a formula with 8 nested if's. Can't you? But wouldn't 7.5 minutes (that's 7 minutes and 30 seconds) make more sense?

Details depend in the programming language you're using, but you should be able to do it in any language you've mastered in a somewhat more than elementary level.

Kees

- Collapse -
Answer
Excel formula ?
Dec 10, 2014 2:28AM PST

I working in Excel

=TIME(HOUR(A2),(MOD(ROUND(A2*1440,0),15)>6)*15+(INT(MOD(ROUND(A2*1440,0),60)/15)*15),0)

This formula above was darn close but still doesn't round 7 min and 50 seconds.
The 7:50 rule is payroll not mine so i can't change it.

Any formula ideas?