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

Formula to identify if time in a cell is within shift schedule or not

0

Hello,

Need your assistance to identify if specific time is with in shift or not. Have already created a simple formula to get it. Its working but if shift covers both AM and PM i am having issues to identify if mentioned time is within shift or not. Below is the sample.

as shown on my screenshot both start time and end time is 10pm and shift declared is 10pm-6:30am. Logically my formula should read it as within shift and should be tagged as YES but due to shift is covering both PM and AM shift my formula is no longer working correctly.

Hope anyone from this group can provide possible solution on this situation.

Have also attached the excel file for your reference.

Thanks in advance

excel
MS Office Version: 2016
posted by: Bhubuli on Wed Sep 13, 2017 at 7:06 am
Answer
Discuss

Answers

0

The first time in your worksheet is represented by the number 0.93474537037037, the second one by 0.936990740740741. These are both good, understandable expressions of time Excel can work with but they can't be compared with the value in your column C, "10:00 PM - 06:30 AM" which can't be related to time. In order to determine the overlap between two time ranges you must define beginning and end of both. Therefore you would have to split your column C into two columns, for start shift and end shift.

There have been quite a few very similar questions recently. Two of them I have picked out for you. Links are below. There was a third one I didn't find right away. You may like to look.

Please read this thread for general information about how to deal with times and dates.

You will find the problem of a shift ending on the next day addressed here

posted by: Variatus (rep: 4889) on Wed Sep 13, 2017 at 7:16 am
Discuss

Discussion

Hello,
Appreciate your prompt response on my query. Though if you will check the file i attached my concern is to clasify if specific time (start time & end time) is within shift schedule declared on the file. Currently if you will check my excel have already created a formula to identify this case. Though the challenge is if shift cover both AM and PM shift. Example 10:00pm-6:30Am, the current computation is not reading it correctly. Hope this clarifies my concern and someone can provide any possible solution on this issue.
Bhubuli Sep 14, '17 at 1:44 am
Your formula does not address the following difficulties. (1) Is the second TimeValue on the same day as the first or the next? (2) Is the time in column(A) before, at or after the start of the shift? (3) Is the time in Column(B) before, at or after the end of the shift? These are issues which should be addressed by worksheet design, not formula design. Your judgment to give up on writing such a formula was 100% correct. Consider, however, even if you find someone to write it for you it will be a monster which will be impossible for you to ever tame and competely own.
Variatus (rep: 4889) Sep 14, '17 at 5:54 am
Add to Discussion


Answer the Question

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