• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

timesheet help

S

stupidname

New Member
Hi,


I'm having a nightmare creating a spreadsheet to help me automate my overtime procedure.


I want the end product to work out the total over time based on date, start time and end time i enter.


I have different overtime policies for weekdays and weekends.


For example Weekedays

1700-2100 (normal time)

2101-2200 (1.5) Time & half

2201-0800 (2.0) Double Time


I want two different totals in the spreadsheet:

1st: Total Time Worked: which is total hours between times - (EndTime - StarTime)

2nd: Total Including Overtime: This column to incororate the overtime policy (1.5, 2.0 etc)


I would upload a spreadsheet, but done so many variations probably complicate scenario.


Basics:

A1: Date (format:ddd dd/mm/yyyy)

B1: Start Time (format: [h]:mm)

C1: End Time (format: [h]:mm)

D1: Total Hours (format: [h]:mm)

E1: Overtime Hours (format: [h]:mm)


if you need anymore information let me know.


Any help is much appreciated.


Thanks in advance


Stu(pid)
 
Now come on, I knows its Friday and the end of the week looms...............but you can not really have a stupid name :)


Good day stupidname


Will this help


https://dl.dropbox.com/u/34893656/Time_Keeping_template1.xlsx
 
Might also be worthwhile to check out this similar thread:

http://chandoo.org/forums/topic/shift-spans-midnight-at-differing-pay-rates
 
Hi Stu(pid)!


First of all welcome to the forum.. :)


Assume your data is in below format,

[pre]
Code: 
Table 1
++++++++++++++++++++++++++++++++++++++++++++++++
Start Time	End Time	Total Work Hours
10:10:00 AM	12:10:00 PM	00:00:00
12:10:00 PM	02:10:00 PM	00:00:00
02:10:00 PM	04:10:00 PM	00:00:00
04:10:00 PM	06:10:00 PM	01:10:00
06:10:00 PM	08:10:00 PM	02:00:00
08:10:00 PM	10:10:00 PM	02:40:00
10:10:00 PM	12:10:00 AM	04:00:00
12:10:00 AM	02:10:00 AM	04:00:00
02:10:00 AM	04:10:00 AM	04:00:00
You just need to create a Helper section for Calculation. like below...

[code]Table 2
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Start Slot	End Slot	HC1	HC2	Time Count
12:00 AM	08:00 AM	0000	0480	2.00
08:00 AM	05:00 PM	0480	1020	0.00
05:00 PM	09:00 PM	1020	1260	1.00
09:00 PM	10:00 PM	1260	1320	1.50
10:00 PM	12:00 AM	1320	1440	2.00[/pre]
HC1 [code]=CONVERT(StartSlot,"day","mn")

HC2 =CONVERT(EndSlot,"day","mn")[/code]

Time Count is the WorkPower..


Now in Table 1 Sections > in Total Work Hour.. Enter Formula as..


=TEXT(CONVERT(SUMPRODUCT(LOOKUP(ROW(INDIRECT(CONVERT(B2,"day","mn") &":"& CONVERT(C2,"day","mn")-1)),$H$2:$H$6,$J$2:$J$6)),"mn","day"),"[hh]:mm:ss")[/code]


where H2 to J6 is in Table 2.


https://dl.dropbox.com/u/78831150/Excel/Timesheet%20Help%20%28StupidName%29.xls


I still not able to handle Days (weekdays & Weekend) as I am little bit hurry.. :(

Hope someone will make it more beautiful.. :)


Regards,

Deb
 
Apologies for the reply all anf thanks for your prompt replies- had an internet outage in work :O so had to sort that out (which will be logged on my new spreadsheet.


bobhc - Thanks for the reply - My name is almost as bad as fagley dork:

http://www.happyplace.com/3683/fagley-dork-man-has-worst-name-of-all-time


Luke M - Thanks for you input - looking into all the exmaples now.


Debraj Roy - Thanks your reply - mate that formula is huge (bet you get that compliment all the time :)) - your example seems like what i need.


Will look into it tomorrow - hopefully get it cracked with your kind help.


Thanks


Stu(pid)
 
Good (not sure where you are so)day stupidname


Pleased you are slowly get to the answer to your post,..........but you gota change your your monicka, people have varying levels of intelligence (look at me) but no person is stupid.
 
Debraj, please can you break down the formula as i'm not sure what its doing:


=TEXT(CONVERT(SUMPRODUCT(LOOKUP(ROW(INDIRECT(CONVERT(B2,"day","mn")&":"&CONVERT(C2,"day","mn")-1)),$I$2:$I$6,$K$2:$K$6)),"mn","day"),"[hh]:mm:ss")


Any help in handling weekday and weekends?


I failed to mention excel is - im make it look pretty!! :D
 
The software is a good idea (very good idea)


But its only 3 of us that will be using it - now that overtime is very limited we won't need as often.


Can someone explain the huge formila please?

Anyone able to handle days of weekends?


Thanks
 
Hi ,


I am sorry but I would suggest you upload your workbook ; there is one point I would like to highlight about the workbook uploaded by Deb , with respect to which you wish to have the formula explanation.


1. The data in columns B and C is labelled Start Time and End Time ; however , the values are actually stored in units of days ; if you see the value in B17 , it is displayed as 04:10:00 PM , which is a time between midnight and 23:59:59 ; however , what it actually contains is the value 40:10:00 , which is 40 hours and 10 minutes. Hence , the CONVERT function can be replaced by a simpler multiplication by 24 * 60


For example , if you use =CONVERT(B17,"day","mn") , you will get the same result as =B17*24*60


In my opinion the CONVERT function is unnecessarily complicating the formula , though it certainly makes it readable.


2. I do not know whether you are using the same data format ; are you using only times , or are you using dates and times / days and times ?


If you cannot upload your workbook , can you post about 20 rows of data with the relevant columns ? Just copy data from your worksheet , and paste it here , between backticks , so that the indentation is preserved.


Also , you have given the table for overtime corresponding to weekdays ; since you say weekends are to be treated differently , can you give the overtime table for weekends too ?


Narayan
 
Sorry for the delay, an example of time worked:

[pre]
Code: 
Date		Day	StartTime	End Time Break	Total Hours  Overtime
16/06/2012	Sat	16:30		02:00	00:00	10:30
17/06/2012	Sun	10:00		20:00	00:00	10:30
06/07/2012	Fri	12:00		01:00	00:00	13:00
12/07/2012	Thu	12:00		22:40	00:00	10:40
[/pre]
1. I need to the total time worked - even if worked till the next day.

2. In the OverTime Column I need the following applied :

Weekday

1700-2100 (normal time)

2101-2200 (1.5) Time & half

2201+ (2.0) Double Time


Weekend

0800-1700: (normal time)

1701-21:00: (1.5) Time & half

2101+ (2.0) Double Time


Cant work out a way to do this!


Thanks in advanced

StupidName!
 
Hi ,


Your data does not seem to be consistent :

[pre]
Code: 
16/06/2012     Sat     16:30     02:00     00:00     10:30
17/06/2012     Sun     10:00     20:00     00:00     10:30
06/07/2012     Fri      12:00     01:00     00:00     13:00
12/07/2012     Thu     12:00     22:40     00:00     10:40
[/pre]
Is the data in the last column referring to the Total Hours ? If so , how do you explain 10:30 in the first two cases ?


To complete my understanding , let me put it in the following words :


1. On weekdays , there are just 4 hours of normal working ( from 5:00 p.m. to 9:00 p.m. ) ; from 9:01 p.m. till 10:00 p.m. is overtime , at 1.5 times the normal rate , and from 10:01 p.m. till 4:59 p.m. the next day is overtime at 2.0 times the normal rate.


2. On weekends , there are 9 hours of normal working ( from 8:00 a.m. to 5:00 p.m. ) ; from 5:01 p.m. till 9:00 p.m. is overtime , at 1.5 times the normal rate , and from 9:01 p.m. till 7:59 a.m. the next day is overtime at 2.0 times the normal rate.


Is this correct ?


Narayan
 
Sorry just quickly made up the data - My hard drive got coruppted so lost all my data :(


Your understanding is totally correct.
 
Hi,


as NARAYANK991 has put my requirements so elegantly in writing (thanks NARAYANK991), can anybody advise a way to produce this so its automated based on the date/time?


Thanks


SN
 
You must log in or register to reply here.
Back
Top