Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Employee Wise Shift Details

0

Hi,

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
excel
MS Office Version: 2013
posted by: chintu12580 on Tue May 23, 2017 at 6:18 am
Answer
Discuss

Answers

0

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.

posted by: don (rep: 1989) on Tue May 23, 2017 at 9:30 am
Discuss

Discussion

Thank you Very much friend,

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

Thanks...
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: 1989) 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: 1989) 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