Trying to extract multiple start and end times during the course of a few days.

**kspeese** · 11-21-2013, 08:46 PM

(Edited - No answers to prior question, maybe this makes it easier for me to receive assistance)

I have been using Excel a lot recently and have created a fairly complicated worksheet with some fairly complicated formulas (after reading and reading), but I don’t even know where to start with this. The included worksheet, I created uses the criteria to the right (Travel Time, Employee Shift, Times before shift and times after shift) to show a visual representation of types of hours associated with this worksheet. Employees may be in travel status for a few days, with anything outside of their shift (8 hours a day) being paid as overtime. The first day’s shift is adjusted to the start of travel times, but the remainder of travel days is their regularly scheduled shift.

The fields highlighted in yellow should be the only fields the end user needs to fill out (will carry over from another worksheet). In columns C, E, G, I, K, and M are validation cells. If the cells contains a “2,” (highlighted Green) then the date / time to the left is the start of a 15 minute block of overtime. The dates and times highlighted in blue are just dates/times that fall within the travel period.

I need to auto calculate the fields I highlighted in orange. There is normally 1 or 2 blocks of OT for each day (So it should be 0, 1, or 2 OT blocks). For each block of OT, I need to figure out if it’s pre, or post shift and what the start and end times are of the OT.

Can anyone help with this?

Thanks

**kspeese** · 11-26-2013, 02:51 PM

No one has any ideas? I have tried a few more things since posting and nothing seems to work. I even tried creating columns between the grid I made, trying to determine if the date in the cell fell between either start and stop date/time, but all came back as value as true, even if it wasn't.

I figure if I could at least something to say if each cell that contains a date and time fell between the travel dates and core hours, I might have another avenue to figure out how to extart the overtime start and stop times.

Any help appreciated.

**kspeese** · 11-27-2013, 01:48 AM

See Original Post - This response not applicable at this time after edit.

**samba_ravi** · 11-27-2013, 01:56 AM

unable to understand what do you want to do exactly.

**kspeese** · 11-27-2013, 02:03 AM

See Original Post - This response not applicable at this time after edit.

**kspeese** · 12-02-2013, 03:14 PM

Now that I re-worded my original post / provided a better sample sheet, does anyone have any ideas on how I can auto calculate the fields in ornage on the sample sheet? Thanks.

**kspeese** · 12-05-2013, 01:03 AM

I solved it for the most part (maybe not the best way, but it works). I'm still fine tuning and came across an issue relating to the 1st day's shift (we change shifts dependent on 1st day's scheduled flight departure time). In the event the flight is at 2:30 AM, and we allow up to 4 hours prior, that bleeds into the prior day. I'm guessing in most cases the employee would work their normal shift on the day before scheduled departure, go home and come back at 10:30PM for a 1.5 hours of Overtime, then have their 1st day's travel shift begin at Midnight. The other option would be to have the employee come in at 4pm and start their shift (complete 8 hours in that day).

Attached is the worksheet in it's completed state. I haven't fully integrated it into my workbook yet due to the before mentioned issue. I'm thinking I could make the 1st day's travel pre-shift OT as a field you could manually enter the data because there should normally not be any pre-shift overtime on the 1st day and then the employee could make a written notation on the printed sheet that the overtime hours listed were for the prior day.

**kspeese** · 02-20-2014, 02:05 PM

I made further improvements to this, as my prior version would not allow for a single 15 minute block of overtime to be listed. It also accounts for 4 different shifts.

Trying to extract multiple start and end times during the course of a few days.

LinkBack

Thread Tools

Rate This Thread

Display

Trying to extract multiple start and end times during the course of a few days.

Re: Trying to extract multiple start and end times during the course of a few days.

Re: Trying to extract multiple start and end times during the course of a few days.

Re: Trying to extract multiple start and end times during the course of a few days.

Re: Trying to extract multiple start and end times during the course of a few days.

Re: Trying to extract multiple start and end times during the course of a few days.

Re: Trying to extract multiple start and end times during the course of a few days.

Re: Trying to extract multiple start and end times during the course of a few days.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] time - start and finsh times on a shift pattern, that covers two days

[SOLVED] Calculate Fractional minutes between half hour intervals with multiple start and end times

Charting multiple start and end times day after day

How do I convert start and end times to duration, days to number of days?

Hours per day over multiple days - wait times

Bookmarks

Bookmarks

Posting Permissions