Sets of Questions for Time Related Data

[maeyks]

Hi,

I am creating my employee's daily time record. However, there are some questions I would like to ask:

Given that A2 = Scheduled Time in
Given that B2 = Scheduled Time out
Given that C2 = Actual Time in
Given that D2 = Actual Time <gs id="da9e0eff-4514-45d8-b4e5-361ad3a78b35" ginger_software_uiphraseguid="32bee9f5-dfaa-49bf-a14f-cc4774ac3587" class="GINGER_SOFTWARE_mark">out</gs>
Break Time is 1 hour
Minimum hours to file over time is 1 hour

What will be the formula for:
1) If the ACTUAL total time (D2-C2-<gs id="93c7f3fa-21c4-448f-a500-914f6b05344f" ginger_software_uiphraseguid="814e2d62-2f1b-4d1e-aba9-b0f218a9687c" class="GINGER_SOFTWARE_mark">breaktime</gs><gs id="8e5273f5-9f92-4b04-837e-40d2a37afbd5" ginger_software_uiphraseguid="814e2d62-2f1b-4d1e-aba9-b0f218a9687c" class="GINGER_SOFTWARE_mark">)</gs>is at least 8 hours, it will return the value of 8
2) If the ACTUAL total time (D2-C2-<gs id="008dcdb2-daa6-4596-9dbf-651cd9d4031e" ginger_software_uiphraseguid="5b51f7ce-2d72-4d1e-a51e-245c35539612" class="GINGER_SOFTWARE_mark">breaktime</gs><gs id="0d9d0d79-0b99-4e19-9572-d89095b759a0" ginger_software_uiphraseguid="5b51f7ce-2d72-4d1e-a51e-245c35539612" class="GINGER_SOFTWARE_mark">)</gs>is at less than 8 hours, it will return the actual time value
3) Over <gs id="62ba1d08-1831-4deb-bbff-95fbc5b5b967" ginger_software_uiphraseguid="a1716ac7-b1b9-41c4-8d22-01e267606025" class="GINGER_SOFTWARE_mark">time with</gs> at least 1 hour from D2
4) Over <gs id="3eeb1c70-e145-4b5c-827a-22e80a88527c" ginger_software_uiphraseguid="8cb00890-ad78-4816-882c-00c0e569f51e" class="GINGER_SOFTWARE_mark">time with</gs> at least 1 hour from D2 and increments of every 30 minutes (for example, 8-5, OT starts at 6pm (1 hour), 6:30pm (1.5 hours), 7:00pm (2 hours), so on UNTIL ONLY 10 pm,
5) Over time upon reaching 10 pm will be considered with NIGHT DIFFERENTIAL PAY; for example, duty was 8-5pm, actual time in was 8:00 am, finished work at 11 pm, therefore OVERTIME is from 5 pm to 10 pm (5 hours) and NIGHT DIFFERENTIAL is from 10 pm to 11 pm (1 hour)

For now these are my questions. Anybody can help me out on this?
Thank you.


Mike

 

[easy2understandexcel]

Hi.
For reference, an hour is equal to 1 divided by 24 (because a day is equal to 1.0)

1&2) if(D2-C2-(1/24)>=(8/24),8/24,D2-C2-(1/24))

for the rest of the questions I dont understand what you're asking...
But, it should be pretty straight forward if you want to clarify a little.

Hope I can help!

Please check out my excel youtube channel Excel 101

 

[gaz_chops]

For the 1st 1 try

=MIN(D2-C2,"8:00")

For the O/T try

=MAX(0,MIN(D2-"18:00","4:00"))

 

[gaz_chops]

I think I misread the O/T part! Try

=MAX(0,MIN(FLOOR(D2,"00:30")-"17:00","4:00"))

 

[gaz_chops]

Oops missed the > 1hr

=IF(D2>=TIME(18,0,0),MAX(0,MIN(FLOOR(D2,"00:30")-"17:00","4:00")),0)