trying to make a sheet that lists the hours of the day down column A

[jvoortman]

trying to make a sheet that lists the hours of the day down column A, and the days across row 1, object is to mark each cell with one of three persons names. my Dad has Alzheimer's disease so we have 24/7 care givers/helpers and there a three different ladies and I need to keep track of their hours, and different shifts pay differently, so if someone covers for someone else they should get that pay rate. shift 1 is Monday to Friday from 8 am to 6 pm (pays $18.50/hr, cooking and light cleaning), shift 2 is Monday at 6:00 pm to Sat 8:00 am (pays $16/hr-no meal feeding), shift 3 is from Sat 8:00 am to Monday 8:00 am (pays $15/hr), any help would be greatly appreciated, john

 

[NiMip]

Sorry to hear about your dad. How's this? (feel free to download the file) https://drive.google.com/open?id=0BzBb8ottVnYhZUx4N2VPTmRWY2s

You enter the names in the hours-days table you asked for on the left, and the summary table one the right tells you how many hours each person worked and how much they should be paid. The colours are just for visualisation of the shifts.

The names are a bit of a pain to manually enter due to the hourly breakdown. If you're not expecting that a shift would ever be partly covered by someone, it might be worth merging the cells to make it easier to populate.

 

[jvoortman]

YOU MY FRIEND ARE AMAZING! one minor problem, why when i change the pay rate to a 18.75 for example, it rounds it up to 19? other than that...THANK YOU SO MUCH!

 

[jvoortman]

I have made a mistake which hopefully is easily correctable, the pay period goes from Wednesday to Wednesday, but when i "cut" the Monday and Tuesday columns and "paste" them after Sunday it all goes haywire, so sorry

 

[jvoortman]

i used "Ctrl H" to change all the names easily, and if shifts ever get split which i don't expect i'll do it manually, lol

 

[NiMip]

The $18.75 displayed as $19 is a formatting issue. The value of $18.75 is still there, but it thinks that you don't want to see any decimals so it displays it rounded to a whole number. I've fixed that for you.

I've done this fairly clunkily (not sure that's a word haha, but I'm rolling with it) because the shifts are broken up into different sections of the roster, so I can't just put one range in for each shift. Long story short, I'm afraid to change anything takes a bit of manual readjustment. I've rearranged to have the week start on Wednesday; hopefully this is what you're after?

Good call on ctrl-H to change the names! Selecting parts of the roster table beforehand will make sure you only replace the bits of the roster you want (if you weren't already doing that). Handy for later if you want to just change a shift and not the names in the summary table.

Updated file: https://drive.google.com/open?id=0BzBb8ottVnYhdldBTnY3QVI0cm8

 

[jvoortman]

FANTASTIC! THANK YOU SO MUCH! exactly what I need..........John