Employee Wise Shift Details



I have in time out time and i need a clarificaiton on shift.

I Need condition on out time and if time is between 11 pm to 1 am (checkout time) then Evening Shift, 1 am to 6.30 (checkout time) then night rest all are in day shift..!

Let me know if you guide on this...!

Preferred Name Calculation Tags In Date In Time Out Date Out Time A   4/11/2017 1:00 PM 4/11/2017 7:28:00 PM B   4/11/2017 1:00 PM 4/11/2017 10:00:00 PM C   4/11/2017 1:00 PM 4/11/2017 10:45:00 PM D   4/11/2017 1:01 PM 4/11/2017 10:58:00 PM E   4/11/2017 1:04 PM 4/11/2017 10:05:00 PM F   4/11/2017 1:05 PM 4/11/2017 10:12:00 PM G   4/11/2017 1:06 PM 4/11/2017 10:19:00 PM H   4/11/2017 1:10 PM 4/11/2017 11:22:00 PM



Here is one way to do it:

=IF(OR(HOUR(A1)=23,HOUR(A1)=0),"Evening Shift", IF(AND(HOUR(A1)>=1,A1<=0.27084),"Night Shift","Day Shift"))

This assumes the time is in cell A1. Try it on a sample time and then change the cell references to work with your template.



Thank you Very much friend,

If you can explain about a wording of A1<=0.27084 will be highly appreciated...!

chintu12580 May 23, '17 at 9:53 am
Time is stored as a decimal and that decimal stands for 6:30 AM. If you type 6:30 AM into a cell, hit enter, re-select that cell, and change the formatting to General, you will then see that number, or one very close to it.
don (rep: 1665) May 23, '17 at 11:06 am
Hi, Thanks. for your prompt response,

I was looking for a some more condition...!

I have five column with a Head of Employee Number, In Date, In Time, Out Date, Out Time. ok.

Employee ID In Date In Time Out Date Out Time 123456789  4/11/2017 1:00:00 PM 4/11/2017 7:28:00 PM

               In           Out Day 10:00 AM 7:00 PM Evening 2:00:00 PM 11:00:00 PM Night 8:00:00 PM 5:00:00 AM       Adding to this..     @30 Min are Grace Period.     If Out time is before 10:30 PM (Evening) should not considered Evening, If Out Time is after 1 am Night then only a Night Shift.
I don't understood how to keep time tracking in this situation...!

If you guide me would be highly appreciated...!

chintu12580 May 24, '17 at 2:43 am
That is different than what you had in the question. Update your question with the in and out times that you need for it to be considered a night shift/day shift/etc. Basically, I will then just update the IF statement so that each condition checks against a decimal number, like the one that checks for 6:30AM.
don (rep: 1665) May 24, '17 at 9:07 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login