24 hour double time

[Sirishgreen]

Hi,

I have a timesheet which for certain days of the year calculates double-time. What i would like to do is have a formula that gives double time for a 24 hour period. I have a formula setup with a start time and end time for my double time, but it wont stop at midnight.

let me know if you need more details.

The formula i use is

Please Login or Register  to view this content.

[daddylonglegs]

That looks like one of my formulas .....

I presume R10 is start time, S10 is end time and R41 and R42 define the double-time period.

What do you have in R41 and R42?

What do you mean, it won't stop at midnight, can you give some examples of shifts you have and results you'd like?

[Sirishgreen]

yep it was your formula that i used thank you!

on christmas day for example double time is paid from midnight to midnight. The 24 hour duration of christmas day. If i enter 00:00 as the start time and 24:00 as the end time i dont get the expected result.

An employee might work from 20:00 until 03:00 and the double time should stop at midnight and dosn't when i enter the above. If i enter 23:59 then it works its just that extra minute that throws it out.

cheers

[Sirishgreen]

bump ** Sorry about this but i havnt had a reply.

[Sirishgreen]

bump * anybody?

[royUK]

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Moved to Excel Functions

[techmob]

bump*

anyone know the answer to this?

thanks

[techmob]

anyone?

is this very complex to even the excel guru's out there>?

[daddylonglegs]

Hello techmod,

Sirishgreen asked the original question, do you have the same question or are you the same person?

The formula is designed to count all hours within the designated period, so if you show start time as 00:00 and end time as 24:00 it counts all hours in the shift, so for your example it will give 7:00. If you change the 24:00 to 23:59 it will still count all the hours except for the 1 minute from 23:59 to 24:00 so result will be 6:59.

You seem to want to apply different conditions to different days, so that 0:00 until 03:00 would be paid double time on Christmas day but not on the day after.......but my formula is specifically set up to do the reverse, i.e. treat all times the same, no matter which day.....

Up shot is that you need a different formula.....or some other approach

Is Christmas day treated like other holidays....? Perhaps you need to have dates included, then you can specifiy different double time conditions for MF, Sat, Sun, holidays etc. otherwise I don't think you can solve it.....

If you could supply that sort of information then I'm sure a formula can be written....or do you need flexbility to specify different double-time conditions?

[techmob]

hi

it is the same question and the same timesheet i am working on

yes different conditions to different days

these are the days and times that double time needs to be applied

From 1800 hrs Christmas Eve to end of shift – Double Time

Christmas Day (to midnight) – Double Time

From 1800 hrs New Years Eve to end of shift – Double Time

New Years Day (to midnight) – Double Time

christmas day is the same as new years day, so i suppose there is going to be 2 formulas needed?

maybe this is the other forumla?

=(P7>Q7)*MEDIAN(0,Q7-R41,R42-R41)+MAX(0,MIN(R42,Q7+(P7>Q7))-MAX(R41,P7))

To calculate the shift length

=MOD(Q7-P7,1)

Note: for the first formula I assume that R41 is less than R42, e.g. double time period is something like 19:00 to 23:00. Formula won't work if you define R41 as 22:00 and R42 as 01:00.

If you want to do that then you need to do the process in reverse, i.e. show R41 as 01:00 and R42 as 22:00. This will then give you the hours worked that are not double time. Deduct this from the shift length to get double time

which would work with the attached timesheet?




i am new to excel so any help on this would be a huge help

thanks for your response

[techmob]

can anyone help please?

[techmob]

just bumping this, need help with this guys?

any help greatly appreciated