(Help) Time Sheet total hours worked per day and overall week

Hello,

I am having difficulty with sorting out a time sheet.

We have 8 tabs
Tab 1 - Hours
Tab 2 - Sunday
Tab 3 - Monday
Tab 4 - Tuesday
Tab 5 - Wednesday
Tab 6 - Thursday
Tab 7 - Friday
Tab 8 - Saturday

The hours tab is split down for each day and will look up all the names for each day. (staff change over the week or change location on sheet.)

then on the hours tab we lookup the date and person name to find the start hours and end hours and should then show total time worked

E.g
Start Time 3.30pm
End Time 6.30am
Total Time 15.00

Start Time 6.30am
End Time 3.30am
Total Time 9.00

At the moment for this to work on the individual day tab i am having to put Start Time 4/4/10 15:30 End Time 5/4/10 6:30
Then on the hours i have a tab just after the start and end that gets the number value of the date and is times by 24 then the total is worked out by taking away the numbers. (is there a way where i can just but the hours in with out the date?)

Then on the hours tab there is a weekly total. This has all the agents names it will then look up each day and fill in the hours for the agents each day.
It will then show total hours for each day as well as total hours for staff per week along with all staff total hours.

I would also like it to not show 12:00am if there is no agent or hours worked and would like it if its 0 hours worked to leave the cell blank.

I have noticed that at moment for like 2 hours 15 mins it will show as 2.25 which is there a way to get it to display as 2 hours 15mins in the total hours box?

*The Sunday to Saturday tabs would have alot of other data on but to work on this i have just gone with the basic information.

Time Sheet.xlsx

Thanks for any help given.

I had to do a bit of reformatting to make copy & paste easier. How does this look?

Thanks this looks like it will be able to do the task that is required i will test this on the version of report i am working on if any issues will come back if all fine will mark this as solved.

Originally Posted by vogel997

I removed the date from the daily tabs as requested

This all works well.

Just trying to see how i could get the hours to work as a vlookup thing as the names may not be on each day so can not just guess which cell they will be in.

I have tried to display this on the original hours tab.

Try this version out...

Thanks Vogel997 this looks great only problem is i am trying to use this on Excel 2003 so the =IFERROR(MOD(D4-C4,1),0) dose not seem to work, what would i do to fix this?

Why not just use the very simple: =IF(B4="","",(D4-C4)) and make sure formatting is set to h "h "mm "min"

Originally Posted by Glenn Kennedy

Why not just use the very simple: =IF(B4="","",(D4-C4)) and make sure formatting is set to h "h "mm "min"

I tried to use that but this seem to not work if the shift hours is for example 6pm till 6am it will now show hours or mins worked it will show as N/A or Nill.

I have attached the full report i am working on (Not Completed), i am still having issues with the hours, once this is working for 1 agent i can then get it working for the rest just having difficulties working out how to code it correctly to work.)

I have put same names and hours on here for an example just to show what i am trying to do.

Try

=IF(M6<L6,(M6+1)-(L6),(M6-L6))

Same as vogel 1997, except set for the data in post 12

Thanks for the help you guys have given me.

With alot of vlookup references i am able to grab most of the information that is needed.

This code *=IF(F5="-","",MOD(G5-F5,1))* Works great and if - is in the hours it stays blank and then if there is hours in then works out the hours and mins with no issues at all.

I have just got the issue with the getting the hours.

I have used code *=IF($A5="","",IF(ISNA(VLOOKUP($A5,Sunday!$J$4:$BK$39,8,FALSE)),"-",VLOOKUP($A5,Sunday!$J$4:$BK$39,8,FALSE)))*

Which is A5 is blank then it shows "-" which is fine but i am having the issue that if A5 has a name in it will do the lookup but the look up is blank and instead of staying as "-" it is doing 12:00 AM.

What can i do to stop it showing 12:00 AM and to show "-" if the cell it is getting the data from is blank?


I found the answer in the formatting.
reformatted cells to *h:mm AM/PM;;-* so if lookup is blank will show "-" and if lookup hours will show "3:15 PM"