Guy's
I've got this spreadsheet that i need to change. Our timesheets now need to go from the 1st to the last day of the month (30th,31st etc) How get the dates in rows b to change according to the month in I4 and show only the 1st to last days of that month.
Also this may be a little more complicated , How do i get the spreadsheet to automatically work out time&half hours and double time(sunday) hours. Obviously the positions will change depending on the month.
thanks for you help.
Last edited by jsmity; 01-03-2008 at 11:04 AM. Reason: didnt make sense
Hi jsmity,
Here's one way with your dates, put your start date in I4, then in B14
=I4 then in B15
=B14+1 drag this down to B41, then in B42
=IF(MONTH(DATE(YEAR($I$4),2,29))=2,B41+1,"") this will take care of leap years
then these will take care of 30 or 31 day months, in B43
=IF(MONTH(B42+1)=MONTH($I$4),B42+1,"") then in B44
=IF(MONTH(B42+2)=MONTH($I$4),B42+2,"")
With regard to overtime you don't mention when the time + half kicks in? Is it before 9:00am
Take a look at this link for some formulas on calculating time
http://www.cpearson.com/excel/overtime.htm
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
thanks for the help on the 1st problem, i dont think i explained myself for working out double time. I know how to workout hours the problem is i need to have a total for both time&half and double time. double time is only paid for sunday. As each month is different i cant just use a standard sum to total time&half and double time.
Is there a way excel can find sunday's and total the corresponding hours to give a total double time ?
To calculate the double time for Sundays, then you could use this in E14
=IF(WEEKDAY(B14)=1,(D14-C14)*2,"") copy down
thats originally what i was trying to do but it wont work as all i want to do is total the number of hours worked on a sunday:
Monday to Sat paid at time&half
sunday paid at double time
but this is done via they payroll dept what i need to give them is the total hours at time & half and total at double. I could do it manual each month but as i have to do it for 32 people that would take a while so was hoping i could get excel to do it for me.
Try this with a helper column, in P1
=IF(ISERROR(WEEKDAY(B14)),"",WEEKDAY(B14)) copy down to P44
In C9
=SUMPRODUCT(--(P14:P44=1)*(E14:E44))
In C8
=SUM(E14:E44)-C9
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks