Identify and populate no. of shifts involved in between 2 dates

**RavindraK** · 12-12-2017, 03:52 AM

Hi Excel Experts,
I would like to prepare a summary of the attached report, where
Criteria for Day : Time from 8:00 to 19:59
Criteria for Night : Time from 20:00 to 7:59

Input.
OPS_STARTDATE OPS_COMPLETED
01-10-2017 14:30 01-10-2017 17:50
01-10-2017 01:52 01-10-2017 11:30
01-10-2017 05:00 01-10-2017 12:00
01-10-2017 06:25 01-10-2017 12:35
01-10-2017 12:48 01-10-2017 16:30
01-10-2017 10:45 01-10-2017 10:45
01-10-2017 09:10 01-10-2017 16:00

Now considering above time (shifts), can we identify and populate no. of shifts involved in between 2 dates (OPS_STARTDATE & OPS_COMPLETED).
Please ref. attached file for desired report / below snap is from attached file
Day1 Shift1 Day2 Shift2 Day3 Shift3 Day4 Shift4 Total No. of Shifts involved
02-Dec-17 Day 02-Dec-17 Night 03-Dec-17 Day 3

File is enclosed for ready ref. (in the file I have done manually)
Can anyone please help me to do it automatically.

KRavindra

**Keebellah** · 12-12-2017, 05:34 AM

I'll see what I can do for you. Just to be sure can it be that the OPS_COMPLETED is the same daye and also day?
02-Dec-2017 15:00:34 completed 02-Dec-2017 19:59:00 this would then only be one Day shift?

**RavindraK** · 12-13-2017, 06:20 AM

Keebellah,
Thanks for quick response

Considering following criteria for Day & Night Shift

for Day : Time from 08:00 to 19:59
for Night : Time from 20:00 to 07:59

Needs to be identified and populated no. of shifts involved in between given 2 dates (OPS_STARTDATE & OPS_COMPLETED).

For Example..
From OPS_STARTDATE : 02-01-2017 18:01:43
To OPS_COMPLETED : 03-01-2017 19:00:00
There are 3 Nos. Shifts involved (Identified 3 Shifts)

And these Shifts are..(populated 3 shifts)
1. 02-Dec-17 Day
2. 02-Dec-17 Night
3. 03-Dec-17 Day

Thanks in advance.

**Keebellah** · 12-13-2017, 12:16 PM

Will see what I can do for you

**Keebellah** · 12-13-2017, 06:42 PM

You attached a non-macro file (xlsx extension), is a macro an option?
In you sheet you only allow for four (4) days does this mean that the start data and time and end date and time cannot be more than one day different so it's really only two days but a max of 4 shifts
How is that?

**Keebellah** · 12-13-2017, 07:46 PM

I think I managed it with only formulas. There's a hidden column N as helper

just enter the train number the start date and time and the end date and time
Hope it helps you

**RavindraK** · 12-14-2017, 01:19 AM

Thanks Keebellah,
Yes, you formulated the desired report, but after changing OPS_COMPLETED or OPS_STARTDATE date&time (especially more than 3 shifts)..

It is not working...

For Example..
From OPS_STARTDATE : 01-01-2017 18:01:43
To OPS_COMPLETED : 05-01-2017 19:00:00
There are 9 Nos. Shifts involved (Identified 9 Shifts); if identification of 9 shifts is not possible atleast 4 Shifts needs to be identified here

And as we made provision of only 4 shifts, first 4 shifts should be populated here (It's OK; no issue) )
1. 01-Dec-17 Day
2. 01-Dec-17 Night
3. 02-Dec-17 Day
4. 02-Dec-17 Night

But it is not working likewise. Can you please look into the same.

I'm sure you will defiantly resolve the same.

Thanks in advance.

Kravindra

**Keebellah** · 12-14-2017, 01:56 AM

If you fill it in like this?

Please Login or Register  to view this content.

**Keebellah** · 12-14-2017, 02:03 AM

You will have to look at the formulas and correct the section if the time is before 07:00 then it's still the day's before Night shift
Or a lot of extra help cells or a macro to process this all.
Maybe the last would be a better option.
A userform to enter the details like train number start date and time of shift and end date and time of shift and then process and split across more than one row like I showed in my previous (#8) answer

**RavindraK** · 12-15-2017, 02:18 AM

Thanks Keebellah,

You have almost done all, only thing is, in case of the diff between OPS_STARTDATE & OPS_COMPLETED is more than 4 shifts it is not working..
My request is..

1. Can we add 2 or 3 more shifts(column) likewise after 4th shift (which makes provision for 6-7 shifts in totality)

2. If option 1 is not possible, considering provision of 4 shifts only, can it be modified that in case of the diff between OPS_STARTDATE & OPS_COMPLETED is more than 4 shifts, 1st 4 shifts from OPS_STARTDATE are to be displayed.

Can you please look into the same.

Thanks in advance.

Kravindra

**Keebellah** · 12-15-2017, 03:11 AM

Well you have seen how I did it so it won't be that difficult, just add as many as you want but it will take time to modify the formulas.
I have shown you how I would do it and all you have to do is expand it, the IF conditions will be the ones to keep in mind,
The hidden Column N will have to be moved since it shows the number of days between OPS_STARTDATE & OPS_COMPLETED

Excel has some (minor) limitations but the greatest limitation is the user's own fantasy and imagination.

If you really get stuck I might take the time but I think that with the sample you have in hand you can solve it yourself.
Happy coding

**Keebellah** · 12-18-2017, 04:11 AM

And, have you given it a try?

**RavindraK** · 12-20-2017, 08:33 AM

Thanks Keebellah for your guidance,
Accordingly have I tried to modified formulae and dates, however, it is not working...

**Keebellah** · 12-20-2017, 11:06 AM

I started on it too, but it requires quite a lot of ifs and more.
I would suggest a macro to do it especially if you consider up to 4 days (could result in 8 shifts)

**Keebellah** · 12-20-2017, 11:06 AM

My suggestion, just add lines for same train and next dates with no more that a 2 day difference

**RavindraK** · 12-20-2017, 11:28 PM

Thanks Keebellah for your guidance and suggestion,

- Possible to consider upto 4 days (result in 8 shifts)

- but adding lines for same train is not seems practicable.

**Keebellah** · 12-21-2017, 01:44 AM

Okay, I'm sure you have a good reason for that.

**bebo021999** · 12-21-2017, 09:32 AM

I tried to add up start time to 0,12,24,36,... hours to get time of shift 1,2,3,4,...then check which shift that the time fell in

Number of shifts can be up to as many as needed.

Day 1, E3:

Please Login or Register  to view this content.

Shift 1, F3:

Please Login or Register  to view this content.

Copy E3:F3 accross up to 10 shifts

**RavindraK** · 12-22-2017, 11:37 PM

Hi bebo021999,

Thanks for your great response and sorry for my late revert.

Your formula is almost done as expected, excluding some day-swap issue.
in Day2, Day4, Day6 and Day8 dates are showing 1 day+1

For example : If Day1 is 02-Jan-17 and Shift1 is Day, Day2 should be 02-Jan-17 and Shift2 should Night.......however as per the formula Day2 is showing 03-Jan-17 and same in next shifts also.....

Please ref. attached file for details

Thanks in advance

kravindra

**Keebellah** · 12-23-2017, 06:33 PM

Hi Kravindra,
I tried with formulas but that's not my thing, there are too many conditions so I wrote a macro.
The macro is triggered only after both the date and time in column B AND C are filled.
The macro will not fill any columns after column X so the formula in column Y remains intact

See next post for the modified file

Happy Holidays and a Excel(lent) New Year

**Keebellah** · 12-23-2017, 07:49 PM

A minor modification

**RavindraK** · 01-09-2018, 12:15 AM

Keebah,

Sorry for late response as I was unable to access the said forum properly.

It's working fantastically, excluding a minor error..

OPS_STARTDATE OPS_COMPLETED FIRSTLIFT_TO_LASTLIFT_MINS Involved shifts
Day1 Shift1
04-01-2017 07:00 08-01-2017 15:23 6263 04-Jan-17 Night

04-01-2017 08:00 08-01-2017 15:23 6203 04-Jan-17 Day

In 1st case (where OPS_STARTDATE is 04-01-2017 07:00 ) the Day1 should be 03-01-2017 and Shift1 should be Night
whereas it showing as ...

04-01-2017 Day

In 2nd case, criteria is perfectly matching

all other concept is perfect.

Kravindra

**Keebellah** · 01-09-2018, 01:50 AM

Good morning, it's good to hear you finally had the time to read it and it's fine to read it works, I'm sure you can manage the minor changes since you have the code to work on and it will give you practice.
The fact the if the start time is before 08:00 and it should be night is my mistake, like I say, you have the vba code.
Give it it a try. Happy coding

**Keebellah** · 01-09-2018, 05:23 AM

It is not that I doubt your skills, but the modification is a little more complicated.
Especially the fact that my code is not that well documented.
I made the necessary modifications and I think this is more like what you want
I have already thought of your next possible question but let's wait and see

I have attached the modified file (same name)

PLEASE USE THE FILE IN THE NEXT POST

**Keebellah** · 01-09-2018, 05:49 AM

Here is what I suppose could have been your next remark
If the end date's hour is less than 8:00 then the last entry should be the previous day Night

**Keebellah** · 01-09-2018, 09:44 AM

Did my best for you.
I added comments to the VBA code and cleaned the code up a bit.
Hope it works for you and that you take the time to at least inform if it works
I do this because I like challenges

**RavindraK** · 01-09-2018, 11:10 PM

Perfect !!!
Keebah you are really great / charm !!!
Thanks for your great help and guidance
Kravindra

**Keebellah** · 01-10-2018, 04:34 AM

Good to her.
Enjoy

**RavindraK** · 01-10-2018, 11:14 PM

Keebah,

You have structured the code perfectly, however, I am facing a small issue but it consuming a lot time.

- I will have to copy and paste OPS_STARTDATE & OPS_COMPLETED in bulk, however, unless and until not clicking to the cells the code is not running, manually I will have to double click n numbers of time i.e. 1000 of times, if I have 1000 records.

Can you please advise / help me in this concern.

I sincerely regret the inconvenience caused to you.

Kravindra

**Keebellah** · 01-11-2018, 01:49 AM

That was the question I was waiting for

Do you have any knowledge of VBA?
You code a a macro to process a for r = x to lastx for each row that has two dates completed like the trigger condition in the worksheet.
And invoke the main macro to process.
Try it an if you get stuck show me what you've tried and I will guide you, don't become dependent of others

**Keebellah** · 01-11-2018, 02:55 AM

Here's the file I had ready for you.
Take a look and maybe it helps you with future explouts with VBA

**RavindraK** · 01-11-2018, 03:21 AM

Keebellah you are really great

Frankly speaking, you are not only resolving querying but your try for educating vba to learner like me is really appreciated, just Hats off to you BOSS !!

Kravindra

Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Re: Identify and populate no. of shifts involved in between 2 dates

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] .AutoFilter compatibility between Excel 2007 & Later versions when dates are involved

[SOLVED] using a timestamp to identify work shifts

Bookmarks

Bookmarks

Posting Permissions