I'd like to round up and devalue my attendance data.
I'd like to round up every 30 minutes.
The working hours are as follows, and the break time is 70 minutes, so the work hours are not separated by clean units.
Office Hours: 8:30 to 17:40
[Example]
Going to work: 8:01 → 8:30
Going home: 17:41 → 17:40
Going to work: 8:29 → 8:30
Going home: 18:09 → 17:40
Going to work: I want to display 8:30→8:30
Going home: 18:10 → 18:10
Going to work: 8:31 → 9:00
Going home: 18:11 → 18:10
I looked it up and found that using CEILING and FLOOR functions can be rounded up and down, but
I didn't understand the round-up and devaluation in 30 minutes from any time.
I would appreciate it if you could give me some advice.
Thank you for your cooperation.
excel google-spreadsheet
The break time is 70 minutes, so you can't leave work in clean units.
I don't know.
I think I can do it if I make a conditional expression with IF and AND functions.
=IF(H1<TIME(8,30,0), "8:30", IF(AND(H1>=TIME(8,31,0), H1<TIME(9,1,0))), "9:00")
=IF(H2<TIME(18,10,0), "17:40", IF(AND(H2>=TIME(18,10,0))), "18:10")
Going to work = TIME(HOUR(A1), CEILING(MINUTE(A1), 30), 0)
Going home = TIME(HOUR(A2), FLOOR(MINUTE(A2)-10,30)+10,0)
© 2024 OneMinuteCode. All rights reserved.