• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Identify end of a shift dd/mm/yyyy hh:mm

Hi, I've been tasked with having to do some analysis around finish times. As this is for a hospital their shifts are 24 hours, and I've been unable to identify the end of their shifts easily before I even start analysing the data.

In the attached I am trying to identify the end of their shift in column E - any help would be greatly appreciated.

Kind regards,

Kelli Webb
 

Attachments

  • Comprehensive Audit report.xlsx
    26.9 KB · Views: 6
Kelli Webb
Are those only possible date and time formats which You could get?
What kind of output would You like to get?
I offer one kind...
 

Attachments

  • Screen Shot 2017-10-18 at 10.15.05.png
    Screen Shot 2017-10-18 at 10.15.05.png
    39.4 KB · Views: 7
Hi Vletm, Yes they are the only date and time formats.

Ideally, I would like to identify the end times of their shifts. I then will use this to understand impacts of changes made to exception reporting etc.

Kelli
 
Hi ,

Can you please explain how the data in columns C , E and F are related ?

Can you explain using the data in rows 16 , 17 and 37 as examples ?

Narayan
 
Kelli Webb
I offered one kind of output,
Those are end times of working days (as shifts are 24hrs).
But as I tried to ask ..
What kind of output would You like to get?
 
Hi Narayan, That example was a bit confusing. I've updated the attachment to include purely the emp no. date and timecard swipes

It appears to be relatively straight forward, except for rows 6, 7 & 8

The employee was scheduled to finish at 22:30 but didn't clock out until 23:03

Just some background - This is not included in the original shift because from 22:30 till 23:03 was initially deemed to be an exception for the manager to review and mark as reviewed not to be paid or approve for OT, in this case it was approved as OT. If not, it would of been shown as 15:57 in and 23:03 out - but only paid as per the scheduled shift i.e. 16:00 - 22:30

I hope this makes sense, the data can be so subjective and difficult to relay all incidences.
 

Attachments

  • Comprehensive Audit - identify out.xls
    33 KB · Views: 3
Hi ,

Sorry , but I am still confused.

Is your data in 4 columns , and you want an output in a fifth column , say column E ?

If yes , then what would the output look like ? How are we going to determine shift timings based on entered data ?

For example , you mention :
The employee was scheduled to finish at 22:30 but didn't clock out until 23:03
How can we determine that an employee was scheduled to finish at 22:30 from the data in your uploaded file ?

Can you clarify with a few worked out examples ?

Narayan
 
Hi, Information rescheduled to finish was just fyi to explain why that on employee appeared to have two finish times, one approx.. 30 mins from the last.

Ultimately an employee has a finish time after the commence a shift, I am trying to identify the finish times. My problem is that a shift can start at 6:00 and finish at 14:00 or start at 21:30 and finish at 5:30.

Ideally I just need to identify the end shifts.

The examples given are a small sample of the +53k I need to sift through.

I hope this helps, thanks again for all your help 'it is greatly appreciated'

Kelli
 
Hi ,

Can you confirm that there are only two shifts possible viz. 6:00 hrs to 14:00 hrs , and 21:30 hrs to 5:30 hrs ?

Narayan
 
Hi Narayan, Unfortunately it's a hospital that runs 24hrs - the majority would be on common shifts but there would be some individual agreements to work the hours of their choosing. Hence my problem.
Kelli
 
Hi ,

Then how can we decide which is the real end of a shift given only a punch out time ?

Unless there is a list of the scheduled shift timings to which any punch out time can be compared for verification , there is no way that a shift end time can be determined given only the punch out times.

Narayan
 
As You wrote:
Ideally I just need to identify the end shifts.
 

Attachments

  • Comprehensive Audit - identify out.xls
    55.5 KB · Views: 6
Back
Top