Calculating Start and End times

Hey everyone,

I am trying to create a time sheet and roster with excel. I have attached the template i have created

I have the layout and basic functionality of the time sheet done but i am trying to achieve 2 things that i am having trouble with.

1) with the time sheet, I am trying to calculate the difference in hours between 2 times (but i want the data to be entered in such a way where they just enter the number rather than 12:00:00opm for instance.

2) based on the time sheet i am trying to get excel to pull the information automatically onto a next worksheet where it shows the time each person worked on a graphical format so that it looks like a calendar would for instance.

Thanks in advance for the help

this shoudl work for you first option, for you second, coudl you provide a desired output?

=IF(F9=H9,G9-E9,G9-E9+12)

that should work out for you, but instead of a vlookup, you will need to use an if statement to determine if that hour was worked, something like this:

=IF(AND(TIMEVALUE('Labour Projection and Rosters'!E$9&":00 "&'Labour Projection and Rosters'!F$9)<=$A3,TIMEVALUE('Labour Projection and Rosters'!G$9&":00 "&'Labour Projection and Rosters'!H$9)+IF(AND('Labour Projection and Rosters'!F9="PM",'Labour Projection and Rosters'!H9="AM"),0.5,0)>=A3),1,0)

the TIMEVALUE portion just converts your enterd time into a useable time. and i also added a check incase someone works a late night.

This will account for the 8.5 type senario, as for the shifts ending overnight, technicaly they would move into the next day so ill have to think about how to make that work with your current set up (or you could extend your time ranges into the late night)

=IF(AND(TIMEVALUE('Labour Projection and Rosters'!E$9&":"&TEXT(('Labour Projection and Rosters'!E$9-INT('Labour Projection and Rosters'!E$9))*60,"00 ")&'Labour Projection and Rosters'!F$9)<=$A3,TIMEVALUE(INT('Labour Projection and Rosters'!G$9)&":"&TEXT(('Labour Projection and Rosters'!G$9-INT('Labour Projection and Rosters'!G$9))*60,"00 ")&'Labour Projection and Rosters'!H$9)+IF(AND('Labour Projection and Rosters'!F9="PM",'Labour Projection and Rosters'!H9="AM"),0.5,0)>=A3),1,0)

sheet name changed, try this (confirmed with Ctrl+Shift+Enter)

=IF(AND(TIMEVALUE(LP!E$9&":"&TEXT((LP!E$9-INT(LP!E$9))*60,"00 ")&LP!F$9)<=$A29,TIMEVALUE(INT(LP!G$9)&":"&TEXT((LP!G$9-INT(LP!G$9))*60,"00 ")&LP!H$9)+IF(AND(LP!F34="PM",LP!H34="AM"),0.5,0)>=A29),1,0)

typo in the formula, sorry about that this is for C4

=IF(AND(TIMEVALUE(INT(LP!$F$10)&":"&TEXT((LP!$F$10-INT(LP!$F$10))*60,"00 ")&LP!$G$10)<=$A4,TIMEVALUE(INT(LP!$H$10)&":"&TEXT((LP!$H$10-INT(LP!$H$10))*60,"00 ")&LP!$I$10)+IF(AND(LP!$G10="PM",LP!$I10="AM"),0.5,0)>=A4)," ","    ")

this portion of the formula is converting to a time value, you just need to replace it with an actual reference to the time you want to look at

TIMEVALUE(INT(LP!$F$10)&":"&TEXT((LP!$F$10-INT(LP!$F$10))*60,"00 ")&LP!$G$10)

no problem, ill break it down for you

=IF(AND(TIMEVALUE(INT(LP!$F$10)&":"&TEXT((LP!$F$10-INT(LP!$F$10))*60,"00 ")&LP!$G$10)<=$A4,TIMEVALUE(INT(LP!$H$10)&":"&TEXT((LP!$H$10-INT(LP!$H$10))*60,"00 ")&LP!$I$10)+IF(AND(LP!$G10="PM",LP!$I10="AM"),0.5,0)>=A4)," "," ")

IF Start time <= This Interval And End Time >= This Interval then do this " " or else do this " "

=if(and(lp!$f$10<=$a4,lp!$h$10>=a4)," "," ")

for hours just use

=(H10-F10)*24

and for $ just multiply that by your rate of pay

you would want to do that with formating, right click the cell, go to formating and make sure its formated as H:MM, then you can format the cell with AM/PM in it as "AM/PM"

because you are using a time value, it needs to be an actual time value, you cannot just drop a portion of it out.

you could use 3 cells (Hours, Minute, AM/PM) to build the time value, that might work for you case.

please see attached and let me know if you have any quesitons.

if you want you can hide columns L and M in on sheet LP

your time value formulas were inconsistant, i also fixed up the formulas on the graph so they can fill down.