Time Attendance Increments Conditions

[maeyks]

Dear All,

I have a question, what would be my formula for the following conditions? <gs id="6a841780-960b-49cd-9aa9-942b2a3985b8" ginger_software_uiphraseguid="59e0d5c7-b904-4add-889b-20d2608edbfd" class="GINGER_SOFTWARE_mark"><gs id="c53335df-1166-4934-a913-0a3bb3e70d79" ginger_software_uiphraseguid="1e45b401-312e-4b95-938c-8d33efba2b76" class="GINGER_SOFTWARE_mark">Basically they</gs></gs> are time related concerns:

My time for work is from 7:00 am to 4:00 pm
1) <gs id="e24491c5-1af9-4eed-8618-be1b50597101" ginger_software_uiphraseguid="445c4089-a964-443a-ba97-6bf1bb890f81" class="GINGER_SOFTWARE_mark"><gs id="b01e244a-3233-4a8e-9aba-9b8a585c27e8" ginger_software_uiphraseguid="c35148d5-6474-41c4-8164-3e20c4853eb4" class="GINGER_SOFTWARE_mark">if</gs></gs> <gs id="c8e5de48-55fc-411a-ac35-4ebc21336b96" ginger_software_uiphraseguid="445c4089-a964-443a-ba97-6bf1bb890f81" class="GINGER_SOFTWARE_mark"><gs id="2b06ace4-5b0d-4bd8-ae5f-db6f73db1846" ginger_software_uiphraseguid="c35148d5-6474-41c4-8164-3e20c4853eb4" class="GINGER_SOFTWARE_mark">i</gs></gs> arrive after 7:00 am but not later than 7:30 am, my shift will be from 7:30 am to 4:30 pm
2) <gs id="923bc92c-f5c4-4648-818c-5b4cade5221b" ginger_software_uiphraseguid="18546578-ffe8-4691-97b7-16c793787a8a" class="GINGER_SOFTWARE_mark"><gs id="3730be3f-72a8-43e0-b750-361d1d76589f" ginger_software_uiphraseguid="9dae443a-2aaf-4602-865f-277760a58db1" class="GINGER_SOFTWARE_mark">if</gs></gs> <gs id="0d4a5204-1b68-4415-8ad6-3b4113a9ab90" ginger_software_uiphraseguid="18546578-ffe8-4691-97b7-16c793787a8a" class="GINGER_SOFTWARE_mark"><gs id="93b8856f-4f45-4227-ad57-5cfb3c9379d4" ginger_software_uiphraseguid="9dae443a-2aaf-4602-865f-277760a58db1" class="GINGER_SOFTWARE_mark">i</gs></gs> arrive after 7:30 am but not later than 8:00 am, my shift will be from 8:00 am to 5:00 pm
3) <gs id="7d6baf01-8777-4d24-973a-7ef688996005" ginger_software_uiphraseguid="d4645868-170a-4d7d-9212-7739e1cb55ab" class="GINGER_SOFTWARE_mark"><gs id="f620ffab-b8f4-473e-9765-9374f35f436d" ginger_software_uiphraseguid="28c3e301-21f2-4d0a-852a-1d19a59ccf51" class="GINGER_SOFTWARE_mark">if</gs></gs> <gs id="f6ca8a72-8949-42b3-8d22-6deae8677bea" ginger_software_uiphraseguid="d4645868-170a-4d7d-9212-7739e1cb55ab" class="GINGER_SOFTWARE_mark"><gs id="13180a71-12d1-44bb-9458-963f24a0bb14" ginger_software_uiphraseguid="28c3e301-21f2-4d0a-852a-1d19a59ccf51" class="GINGER_SOFTWARE_mark">i</gs></gs> arrive after 8:00 am but not later than 8:30 am, my shift will be from 8:30 am to 5:30 pm
4) <gs id="608e247c-1521-4387-a321-3228e210153b" ginger_software_uiphraseguid="18c0ba7d-c1fb-40a7-ac69-98af4bd63ed0" class="GINGER_SOFTWARE_mark"><gs id="3c5834a0-006e-44c3-b8de-4504e8a2274c" ginger_software_uiphraseguid="60088ac9-70b6-44d5-a5e2-e9e4986dab7c" class="GINGER_SOFTWARE_mark">if</gs></gs> <gs id="e8ab12d1-8c18-4078-9fff-7a020bb2a73c" ginger_software_uiphraseguid="18c0ba7d-c1fb-40a7-ac69-98af4bd63ed0" class="GINGER_SOFTWARE_mark"><gs id="3b1bf082-d7d0-49ca-b07f-1250ecf3e0e8" ginger_software_uiphraseguid="60088ac9-70b6-44d5-a5e2-e9e4986dab7c" class="GINGER_SOFTWARE_mark">i</gs></gs> arrive after 8:30 am but not later than 9:00 am, my shift will be from 9:00 am to 6:00 pm
5) <gs id="395dac31-0fc1-4945-9152-6c65b90b79a2" ginger_software_uiphraseguid="816ee19c-268d-451b-a458-fc936841bdbe" class="GINGER_SOFTWARE_mark"><gs id="3bf62664-ff3a-490c-8b09-630732a5de31" ginger_software_uiphraseguid="fcb65e40-3943-42eb-9646-1d658ae5f29f" class="GINGER_SOFTWARE_mark">if</gs></gs> <gs id="5c0b2b03-1ac8-44a5-93e7-c12e7c5f31be" ginger_software_uiphraseguid="816ee19c-268d-451b-a458-fc936841bdbe" class="GINGER_SOFTWARE_mark"><gs id="2bafa10a-d52f-47e5-9382-82f3768c97ba" ginger_software_uiphraseguid="fcb65e40-3943-42eb-9646-1d658ae5f29f" class="GINGER_SOFTWARE_mark">i</gs></gs> arrive after 9:00 am but not later than 9:30 am, my shift will be from 9:30 am to 6:30 pm
6) <gs id="e570cbab-1867-4dcc-8860-2b64cba6815a" ginger_software_uiphraseguid="360cd800-f1ed-4c56-b959-600c07d23dfe" class="GINGER_SOFTWARE_mark"><gs id="3c9c4126-2e4b-4753-9858-3a380de06ee0" ginger_software_uiphraseguid="d2d93c35-94c1-4e72-9499-921cffa3dbe3" class="GINGER_SOFTWARE_mark">if</gs></gs> <gs id="4272890b-0379-4b64-bc39-a020663ee663" ginger_software_uiphraseguid="360cd800-f1ed-4c56-b959-600c07d23dfe" class="GINGER_SOFTWARE_mark"><gs id="d8d5570c-5a9f-4afc-9aa5-9d210034538b" ginger_software_uiphraseguid="d2d93c35-94c1-4e72-9499-921cffa3dbe3" class="GINGER_SOFTWARE_mark">i</gs></gs> arrive after 9:30 am but not later than 10:00 am, my shift will be from 10:00 am to 7:00 pm
7) <gs id="c35f782d-896d-41f9-bbec-2af97e670ddb" ginger_software_uiphraseguid="d0be26ee-c586-4bdb-9475-4f9326ee2414" class="GINGER_SOFTWARE_mark"><gs id="e299f25e-595b-47fa-8f0c-b909598afe1b" ginger_software_uiphraseguid="ed842d84-efd2-4ade-b7b3-c6661016d931" class="GINGER_SOFTWARE_mark">if</gs></gs> <gs id="b46ab85d-3112-42d7-9e83-ae4c94a12671" ginger_software_uiphraseguid="d0be26ee-c586-4bdb-9475-4f9326ee2414" class="GINGER_SOFTWARE_mark"><gs id="c4fe4357-4252-4284-844b-0f3c51d1eb31" ginger_software_uiphraseguid="ed842d84-efd2-4ade-b7b3-c6661016d931" class="GINGER_SOFTWARE_mark">i</gs></gs> arrive after 10:00 am my shift will still be from 10:00 am to 7:00 pm, but the minutes incurred after 10:00 <gs id="d18559b1-fd07-4e13-854f-a00454a6a79d" ginger_software_uiphraseguid="dd0adf68-8b1f-4822-859d-d91360696c1c" class="GINGER_SOFTWARE_mark"><gs id="81d90592-27a6-4931-a6f2-e441b2196bdb" ginger_software_uiphraseguid="ed842d84-efd2-4ade-b7b3-c6661016d931" class="GINGER_SOFTWARE_mark">am</gs></gs> shall be considered late; for example if <gs id="7c1c7176-cf50-40f7-954b-292c7ae648b1" ginger_software_uiphraseguid="dd0adf68-8b1f-4822-859d-d91360696c1c" class="GINGER_SOFTWARE_mark"><gs id="8443dc7b-6955-4b02-aacb-3e5771580696" ginger_software_uiphraseguid="ed842d84-efd2-4ade-b7b3-c6661016d931" class="GINGER_SOFTWARE_mark">i</gs></gs> arrived 10:17 am. <gs id="555068ed-c6b6-4473-bb52-ee3a22f3e47f" ginger_software_uiphraseguid="73051ac2-1795-4f8c-9bed-3a108229be85" class="GINGER_SOFTWARE_mark"><gs id="b26bffb9-054c-4377-a9c1-d8a3cc5a3091" ginger_software_uiphraseguid="4d62010b-b0e0-45c9-90e2-487fc84cd383" class="GINGER_SOFTWARE_mark">my</gs></gs> official time will be 10:00 am to 7:00 pm but <gs id="17d8a83a-a392-4376-82e2-340ab022600d" ginger_software_uiphraseguid="73051ac2-1795-4f8c-9bed-3a108229be85" class="GINGER_SOFTWARE_mark"><gs id="6d68608d-997a-4638-bc7c-ac38a0f309d2" ginger_software_uiphraseguid="4d62010b-b0e0-45c9-90e2-487fc84cd383" class="GINGER_SOFTWARE_mark">i</gs></gs> have a <gs id="d3512b33-041f-47b2-abe7-34dccfbaa70c" ginger_software_uiphraseguid="73051ac2-1795-4f8c-9bed-3a108229be85" class="GINGER_SOFTWARE_mark"><gs id="9d14aa18-c576-4462-bad7-2caee8d0784a" ginger_software_uiphraseguid="4d62010b-b0e0-45c9-90e2-487fc84cd383" class="GINGER_SOFTWARE_mark">late</gs></gs> of 17 minutes, therefore my total time is 7 hours and 43 minutes (with 1 hour break time)

Big THANK YOU to those who will be able to help.

Thanks!


Michael

 

[jumbledore]

in cell b1 enter the arrival time, say 7:15 am

in cell b2 enter:

B3-TIME(9,0,0)

in cell b3 enter:

IF(B1>TIMEVALUE("10:00 am"),TIME(7,0,0)+15,TIME(7,0,0)+TIME(9,(INT((((B1-TIME(7,0,59))*24*60)/30))+1)*30,0))

 

[jumbledore]

Can you please check and tell me if the above is working for you? I spent some part of my personal time working on this. Don't you think you should at least have the courtesy to respond to it?

 

[GerryZ]

Can you please check and tell me if the above is working for you? I spent some part of my personal time working on this. Don't you think you should at least have the courtesy to respond to it?

@maeysk Welcome to the Blacklist

 

[jumbledore]

@maeysk Welcome to the Blacklist

you know some people are so selfish and inconsiderate. they get what they want and then don't even bother to respond. I am not looking for a thank you but at least knowing the fact that it helped someone would give me a little bit of satisfaction.

Added maeysk to my ignore list