Spreadsheet formula assistance please

VirtualAdept

VirtualAdept

Suspended / Banned
Messages
2,169
Name
Mads
Edit My Images
Yes
Hi folks in this bit of the forum.
I'm wanting to setup a spreadsheet that I can I put my start time at work, end time and length of break and have it work out what hours I've worked.
To make life a little more complex, I'd like to output a what I should get as gross pay, but there are two issues to that...
1, we get paid at a different rate for part of my shift and
2, I'm only paid in 15 minute increments, so if I clock out at say, 1459, I'd only get paid until 1445.
Is this doable in something akin to excel?

Tia
Mads
 
do you have a fixed start/end time i.e. if you startat 7:45 do you get paid from 8?
What are your pay time zones?
 
Not exactly. I can start at 430am and get night rate til 6am, then work on til 1pm, but I'd actually clock in at 425, and out at 1.10 for example
 
Yes, doable in any spreadsheet, and not a big task, but you'll have to be prepared to do something, a trade secret of the expert cognoscenti very few people know about: read the manual :-)
 
=(INT(B2)*24+HOUR(B2)+(INT(MINUTE(B2)/15)*15/60))-(INT(A2)*24+HOUR(A2)+(ROUNDUP(MINUTE(A2)/15,0)*15/60))

is the basic formula you need.
B2 is time off
A2 is time on
There is no exclusion time which I expect there is.
Roundup start time, rounddown (or integer) end time

@chris malcolm have you ever read a MS manual?
They never refer to the software you are using or as MS would say "it's an undocumented feature"
 
The easy part:

start time in cell a2, finish time in cell b2, break duration in cell c2. Make sure you use proper times (eg 08:32). Put the following formula in cell d2:
=ROUNDDOWN((B2-A2-C2)/(15/1440),0)*(15/1440)
That will give you the hours worked, rounded down to the nearest 15 mins.
 
The easy part:

start time in cell a2, finish time in cell b2, break duration in cell c2. Make sure you use proper times (eg 08:32). Put the following formula in cell d2:
=ROUNDDOWN((B2-A2-C2)/(15/1440),0)*(15/1440)
That will give you the hours worked, rounded down to the nearest 15 mins.
 
Sounds like someone works for a supermarket to me, ASDA maybe
 
chris malcolm said:
: read the manual :)
Click to expand...

Woah woah woah, lets not get silly here. I've googled a fair bit but everything I've seen has gone over my head tbh. Could be because I'm knackered.

Keith W said:
Sounds like someone works for a supermarket to me, ASDA maybe
Click to expand...

Precisely. I've stepped into a section leader role and I'm trying to keep track of what I'm working in a way thats easy to reference when my pay slip comes in.

Thanks for the help guys
 
You get paid in 15 minute increments, WTF?
 
Damn that 1 minute early could be costly. I hope all clocks are time synchronised with your watches.

Can't really add anything more as you've already got good advice.

I wonder whether there is a smartphone app for that to make it even easier.
 
Yes, Phil, I've just input a couple of weeks data into it and its giving me exactly the information I need. Many thanks :)
 
You must log in or register to reply here.
Back
Top