Time & a half calulated on real time (not after x many hours worked)

**nickp84** · 11-03-2012, 10:24 AM

Hi,

Can't quite find what I'm looking for.
I 'm trying to calculate time and a half after a certain time of day not after x many hours worked (which seems to be everyone else's question)

ie Standard time 7.30am-11.30pm, time and a half 11.30pm - 7.30am.

Name Start End
Simon 12/04/2012 12:00 12/04/2012 18:00
David 12/04/2012 22:00 13/04/2012 03:00
etc

I've got the rates on a separate tab, so can just link to them as I need to but need a helping hand with the formula - any ideas? 'IF" and some kind of <>?
Thanks all

N

**dogberry** · 11-03-2012, 10:37 AM

Hi

Can you post a sample worksheet and are the times quoted static or do they vary

Chris

**oeldere** · 11-03-2012, 10:38 AM

post an excel file without confidentional information.

**nickp84** · 11-03-2012, 11:24 AM

Hi,

Here is something I mocked up - Timesheet on one tab, various bands of pay on the next.

Essentially fill in Total Due Column

Cheers all. Thanks for your help

dogberry - times would vary yes, could be anything from 1 to 10 hours (for arguments sake) actually they would be subject to a minimum call but I'm not worried about that at this stage..thanks

N

TimeSheetTest.xlsx

**oeldere** · 11-03-2012, 11:30 AM

Maybe you can also add the desired result in the flile.

**nickp84** · 11-03-2012, 02:57 PM

good idea - slightly rejigged here.

NB - the notes section is just my working out to get the answer - not needed in final.

cheers

TimeSheetTest2.xlsx

**Kevin UK** · 11-04-2012, 05:56 AM

Hi

I would start with dates & times in their own columns. You are over complicating things as well, with overtime between 11:30 PM - 7:30 AM and regular time between 7:30 - 11:30 PM you will need a bunch of IF's, AND's OR's etc. Probably why your not getting much response to your post!

Here's a link to working with Dates and Times

**nickp84** · 11-04-2012, 08:03 AM

Originally Posted by Kevin UK

Hi

I would start with dates & times in their own columns. You are over complicating things as well, with overtime between 11:30 PM - 7:30 AM and regular time between 7:30 - 11:30 PM you will need a bunch of IF's, AND's OR's etc. Probably why your not getting much response to your post!

Here's a link to working with Dates and Times

HI,

Thanks for replying Kevin.

I can't change the time bands (annoyingly) that is just the deal at work. Do you think it is just a little too tricky to do what I want?

Take your point about separate columns - however then you can't work out the possible bridge over two days, (ie Monday 22:00 - Tuesday 03:00 in separate columns returns a shift of -19 hrs).

Unless anyone can think of a workaround,even if slightly convoluted, maybe I'm stuck.

Any thoughts?

Nick

**jason.b75** · 11-04-2012, 08:08 AM

I can do convoluted

Formula:

Please Login or Register  to view this content.

**nickp84** · 11-04-2012, 08:16 AM

well fliberdigibbit! you got it it seems. though I don't really understand it... I'll have to look at the MAX and MOD functions.

Thank you very much.

N

**jason.b75** · 11-04-2012, 08:23 AM

You're welcome,

As an afterthought, I didn't check the results with a start time before 23:30 and finish after 07:30, I think it should be ok though.

**Kevin UK** · 11-04-2012, 08:50 AM

Impressive, but how about row 4 where the hours are 15.5!

I got it! I was looking at what the OP had in E4 & F4

**BB1972** · 11-04-2012, 11:14 AM

@jason.b75,

I was having a look at this problem last night, and decided the best course of action would be to sleep on it - I'm glad now that that's what I did! Good work.

**nickp84** · 11-04-2012, 02:27 PM

Hello -

Bad news - something doesn't quite work. I thought i did at first then realised something wasn't quite right. See attached for illustration.

TimeSheetTest2.xlsx

Seems to be fine if they are working eg 06:30 - 07:30 but if they work 06:00 - 07:00 it doesn't. (No one would every work just a single hour shift of course but for ease)

Result even stranger if they work 01:00 - 02:00. Seems to be continuing to multiply....

Also there is a rogue penny that appears every now and again.

Or maybe it's just me and I've done something weird when I copied the formula...

Thanks for your help thus far!

**jason.b75** · 11-05-2012, 02:56 PM

I see the problem, just having a little trouble trying to figure out a solution, think I need to upgrade my thinking cap

The rogue penny is probably just a rounding issue, part pennies resulting from partial hours rounding up when you're expecting them to round down.

**jason.b75** · 11-07-2012, 01:11 PM

So far I haven't found anything to cause this version to fail, it's ugly, but given what you're working with, it's the best I can come up with.

As Kevin advised in an earlier post, it's much easier to work with the date and time split into separate columns, I've included both versions in the sheet for you to compare.

**nickp84** · 11-15-2012, 05:59 PM

Oh thanks - sorry I didn't reply earlier - I never got the 'someone has replied to your message' email so assumed the thread had gone quiet.

Thanks all for your hard work, and jason for cracking it!

Have a good day!

Nick

**nickp84** · 11-15-2012, 06:12 PM

Oh thanks - sorry I didn't reply earlier - I never got the 'someone has replied to your message' email so assumed the thread had gone quiet.

Thanks all for your hard work, and jason for cracking it!

Have a good day!

Nick

Time & a half calulated on real time (not after x many hours worked)

LinkBack

Thread Tools

Rate This Thread

Display

Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Re: Time & a half calulated on real time (not after x many hours worked)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions