excel time help needed please.

helpimcrap · 2 Apr 2008 at 21:02

hi, i'm really stumped on this one....

i'm trying to work out how many hours on a spreadsheet are worked at premium (unsociable hours) and how to work it out...

e.g.

working 00:00-08:00.... unsociable hours are 18:00-06:00.

how can i get it to work out that 00:00-06:00 will be at a premium but the last 2 hours wont be.

i've tried IF/AND statments to work it out, but i'm having trouble getting them to work because of the crossover of time at 00:00 which means that i cannot just say it in the easier format.

i don't have the formula to hand what i was trying to use, but i could get it to work in a fashion but i need to be able to put in any start or end time and have it work out the premium from that. there are workarounds that could be employed but it's something that's been niggling me and no one i know knows how to do it!

if anyone could point me in the right direction it would help. cheers.

BurningHorizons · 2 Apr 2008 at 22:18

I've emailed you.
It's a bit basic, but it works.
Hope it helps.

Namsnik · 2 Apr 2008 at 22:26

The following solution does not work if Start Time is in one day and end time in another. I simplified the task by setting Unsoicial hours as 00:00 to 06:00 - to give you the idea.
(I assume that you are entering proper Excel times, not just text that looks like a time.)
A B C D E
Start Finish Worked Overtime Standard
00:00 08:00 08:00 06:00 02:00
01:00 08:00 07:00 05:00 02:00
09:00 10:00 01:00 00:00 01:00
Example formula: =MIN(B8,OvertimeEnd)-MAX(OvertimeStart,A8)

Namsnik · 2 Apr 2008 at 22:29

(It would be good if you can share your solution).

helpimcrap · 2 Apr 2008 at 22:36

thanks for the help...

this is the formula i've been trying to use and it's not complete as it won't work out night shift premium yet (i did originally but this is one i've just knocked up so you can get an idea of what i'm after)...

=IF(AND(B11<TIME(18,0,0),C11<TIME(18,0,0),B11>TIME(6,0,0)),IF(B11<C11,C11-B11),IF(AND(C11>TIME(6,0,0),B11<TIME(18,0,0)),C11-TIME(18,0,0),IF(C11<B11,1,0)))

Times
07:00-15:00
15:00-23:00
23:00-07:00

Premium Times
18:00-06:00

these times need to be flexible so that if i need to change the times to 18:00-06:00 then it records 12 hours at premium etc...

Namsnik · 2 Apr 2008 at 23:06

I did write a formula to do something broadly similar some years ago but it got very complex. MUCH easier to create a User Defined Function (using Select Case). The key variables can of course be held in the worksheet.

I find www.tek-tips.com VERY helpful. You could search their forum as I am pretty sure a very similar question has been posted in the past year.

Namsnik · 2 Apr 2008 at 23:14

Don't hard code the 18:00 06:00 times - put them in cells in the workbook, name those cells and refer to them as I did in my formula.
Is it safe to assume that no one will work more than 12 hrs in a single shift?

just noticed I posted the wrong formula: Overtime in row 11 equals
=IF(A11<OvertimeEnd,MIN(B11,OvertimeEnd)-MAX(OvertimeStart,A11),B11-A11)
But subject to the limitation stated before.

helpimcrap · 2 Apr 2008 at 23:26

Namsnik said:
Don't hard code the 18:00 06:00 times - put them in cells in the workbook, name those cells and refer to them as I did in my formula.
Is it safe to assume that no one will work more than 12 hrs in a single shift?

just noticed I posted the wrong formula: Overtime in row 11 equals
=IF(A11<OvertimeEnd,MIN(B11,OvertimeEnd)-MAX(OvertimeStart,A11),B11-A11)
But subject to the limitation stated before.

i put them into this formula more as a show of what i was aiming at... on the actual s/s they are in cells and hidden.

thanks for the help, i'll have a look at that link too.

helpimcrap · 3 Apr 2008 at 22:51

still struggling.... anyone else?

i think i'm about to give up on and put it down as a no-go...

have broke it down into various segments in an attempt to get it to pick it up one at a time and then just sum it and set it to 0 certain cells if other values come through.

Namsnik · 5 Apr 2008 at 12:35

You have set yourself an interesting challenge, but not an unusual one. There are bound to be solutions available out there and people who can guide you through.

1. Did you search/post in that other forum - lots of folk with really in depth knowledge about MS Office and Excel. If you are clear with them and want to learn how rather than someone give you the answer on a plate then that is the place to go.
2. Exactly how are you recording start and finish times? Are these recognised by excel as dates/times. Are you recording the dates as well? Hint format as a custom format HH:MM DD-MM-YY what do you see?
3. I (think I) would record start date and time in column A, end date and time in column B.
4. Is it possible that there could be more than 12 hours between start and finish time? If so the formula gets even more involved.
5. Are you able/willing to go the VBA route and create a User Defined Function for this?
6. Did you get the formula I posted working at all? Did you follow the logic of my formula? EG the use of Max and Min and named ranges?
7. If you record start and finish as dates and times then functions like =A2 - Int(A2) will give you the just the time. Int(A2) would give just the date. There is a FAQ on the site I mentioned that explains this but basically excel records dates/times as numbers. The integer part of the number is the number of days since 1january1900 and the decimal part is the fractions of a day.

If you reply to these questions here or at Tek-Tips.com then I will try to find time to help solve the puzzle but I repeat imho you will get more expert help from Tek-Tips.com as it is dedicated to this sort of issue.