Calculating additional hours onto a shift

[lynzlou]

Maybe cause it's Friday but I am totally stumped here!

In my new role, staff will add x amt of hours to their shift as overtime. For example, John is scheduled to work until 11:00am. He emails to say "I will pick up 3 hrs 15 min of overtime at the end of my shift" can I not put in a formula that says if you add 3.25 hrs to an end time of 11am, he will be working till this time. Also in the reverse where if he starts at 8am and wants to work 3 hrs and 15 minutes overtime before his shift, then a formula where his start is 8am and 3.25 hrs before is a new start time of....

I think the snow in our town is freezing my brain!

[Aardigspook]

You can just add/subtract the times. Let's say you have 'Start time' in A1 and 'End time' in B1, with 04:00 in A2 and 11:00 in B2.
Put in four new columns C-F: 'Early Overtime', 'New Start time', 'Late overtime' and 'New End time'.
In C2 and E2 you can enter overtime requested. In D2, put this:

Please Login or Register  to view this content.

The reason for the IF...>=0 is to account for anyone working overtime prior to a shift which takes the start time to the previous day - Excel doesn't like negative times, so adding 1 to the start time keeps everything positive. If your times are in 'date and time' format, then you can just do a simple subtraction instead (the A2-C2 bit).

In F2, put this:

Please Login or Register  to view this content.

The thing you need to ensure is that all times are entered as times, not decimals. Enter them as '3:15' not '3.25' or '4:00' not just '4' (without the inverted commas). The colon lets Excel know to treat it as a time.

Hopefully that helps.

Edit: see attached file which shows the above formulae working.