hours worked after certain time

Hello,

This is my first post here. I have actually posted this thread in another forum :-

http://www.mrexcel.com/forum/showthread.php?t=630842

but have yet to get a reply.

In cells A1 and B2 I have the start and end times of a shift. These are specified in the format dd:mm:yy hh:mm e.g 23/04/2012 16:00 and 24/04/2012 03:00. I need the number of hours worked after 20:00 hours. In this case the answer is 7 hours.

Note as this is for a hospital, there is not a fixed "ground zero" i.e a fixed point in time where rollover occurs - as there are always staff working.

Thanks

Kaps

2mr_kaps, could you upload a sample file that closely resembles your real-life data?

So, what you need is

Please Login or Register  to view this content.

right??

Unfortunately it's not that simple because of the format... do you have a sample file for us to play with?

See attached sheet, example may help with what you need.

As requested. Thanks

Kaps

And I think my opening post is confusing. When I meant after 20:00 hours I meant the hours worked after 8.00 p.m - not elapsed time in erxcess of 20 hours worked. Sorry for any confusion.

@kaps,

i was testing with some "made up" data and found that my formula is not bringing correct results for the following:

Start Time
23-04-12 04:00

End Time
25-04-12 03:00

i do not know if you will ever have such a case. but since this is a hospital, you never know! i will try to see if i can fix my formula, or may be some expert will help you...

Thank you for your contributions. It is much appreciated. The case of working through two days is not relevant - so if they start on the 23rd then they will stop on the 24th.

I have been testing icestationzebra's formulae- and although it generally works, I have found an example where it gives an unexpected result. See the last example in the attachment where the shift starts at 01:00 and finishes at 09:00 a.m. I would expect an answer of 8 hours. However I am getting 13 hours. thanks

Kaps

thanks - I see what you mean about both being before 8 p.m. However I do need it to give a result of 8 hours in this scenario. Your formulae was better than Daddy Long Legs - (don't put yourself down - I know these formulae are extremely complicated). Can you suggest an amendment ? Thanks

Kaps

Ok - lets say that the existing results are valid as they are - apart from the new case.

Note that I am thinking out aloud here - as there seems to be no easy answer.

In the new case, lets look at the proportion of the shift that is before 6.00 a.m in the morning. If this is greater than 50% then you consider it as part of the previous day. If it is more than 50% then you consider it as a new day.

so 01:00 to 09:00 a.m is 8 hours, 01:00 to 06:00 is 5 hours. 5/8 is 62.5% - which is greater than 50% - so there are 8 hours worked after 8 p.m.

If however somebody worked from 04:00 to 10:00 a.m - which is 6 hours - with 2 hours before 6.00 a.m - this is 33.33% - less than 50% - so it counts as 0 hours.

Sorry if this sounds terribly convoluted but I have struggled with this problem for weeks !!! Any help greatly appreciated. Thanks

Kaps

P.S Sorry Daddy Long Legs if I caused any offence.

thanks everybody for your help !!!