Calculating Start and End times

**jame24** · 03-12-2012, 04:06 PM

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

**DGagnon** · 03-12-2012, 06:13 PM

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

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

**jame24** · 03-12-2012, 07:44 PM

thanks mate....that worked brilliantly

as for point 2, please see attached of what I'm looking to achieve.

Im thinking some form of vlookup would determine what time the sift started and a value such as "1" would be displayed in each cell and a conditional formatting would make it a specific colour?!?!?

look forward to hearing from you.

**DGagnon** · 03-12-2012, 07:53 PM

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:

Please Login or Register  to view this content.

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

**jame24** · 03-13-2012, 08:21 AM

Thanks a lot. that did the trick.

if i wanted to change it so that it can pick up start times at the half hour mark (eg 8:30) how would the formula above and in the first worksheet change to reflect that? i keep trying to vhange the formating and even entering for instance 8.5 but that doesnt seem to be working.

also if from the first worksheet, the start time is in the PM and end time is in the AM, the graph doesnt seem to track it? wondering if you could give some advice on this.

cheers mate

**DGagnon** · 03-13-2012, 01:27 PM

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)

Please Login or Register  to view this content.

**jame24** · 03-13-2012, 08:40 PM

Hey

I tried the formula you made, it does not seem to be working. I have included the updated form i have been working on to see if i have been doing something wrong.

Thanks

**DGagnon** · 03-13-2012, 08:44 PM

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

Please Login or Register  to view this content.

**jame24** · 03-13-2012, 09:15 PM

Hey

When i use 5.5 as a start time the graph doesn't pick it up? any reason why this would happen? sorry for all the questions I have attached the file i am working on now

thanks

**DGagnon** · 03-13-2012, 09:18 PM

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

Please Login or Register  to view this content.

**jame24** · 03-14-2012, 12:58 PM

Hey,

I tried to switch the time so that I can enter the start time as 8:30 as oppose to 8.5 (on advice from my coworkers). How would I do this with regards to the formulas?

I dont seem to be getting it to work as the format it comes in is very rigid in the sense that it comes in the format hh:mm AM/PM (all in one cell). where I would want them to enter just the number in one cell and AM/PM in the other without conflicting

thanks in advance

**DGagnon** · 03-14-2012, 01:09 PM

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

Please Login or Register  to view this content.

**jame24** · 03-14-2012, 01:16 PM

Hey man,

Sorry for the abundance of questions, and i dont mean to sound like such a noob but, im still really confused about what this formula is trying to say or how to manipulate it

thanks again

**DGagnon** · 03-14-2012, 01:23 PM

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 " "

**jame24** · 03-14-2012, 02:51 PM

Ok better now. Thanks

How about the netering of the time as 8:30 instead of 8.5. The formulas are getting messed up because of that. Any ideas?

Thanks again.

**DGagnon** · 03-14-2012, 04:35 PM

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

**jame24** · 03-14-2012, 04:58 PM

Once again..so sorry for being annoying.

for the 8.5 to be entered as 8:30, i was actually trying to get it to work on the LP page so that the hrs and $ can be calcualted properly. any advice?

Thanks

**DGagnon** · 03-14-2012, 05:06 PM

for hours just use

=(H10-F10)*24

and for $ just multiply that by your rate of pay

**jame24** · 03-14-2012, 05:14 PM

Hey,

I more so asking about collmun F and H. When i enter the start time as 5.5 for instance, eveerything works out fine. but i want to change that so i can enter the information as 5:30.

However when i neter it as 5:30, the number comes up as 5:30:00 PM. I want it to just show up as 5:30 since I have the second collumn asAM/PM. the reason i dont want it be as 5:30:00 PM is because it will be too complicated for the people using it to understand as they are not very proficient at excel( infact almost beginners).

Is there a way to just enter the info as 5:30, it stays in that format and hrs can be calculated automatically from that (using the AM/PM columns i have. I even tried adding a hidden collumn that can aggregate the info but cant seem to figure it out.

Thanks once again

**DGagnon** · 03-14-2012, 05:18 PM

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"

**jame24** · 03-14-2012, 05:27 PM

when i enter 5:30 in F and AM in G, in the actual cell, it shows up as 5:30, but on the formula bar (under the ribbon) it shows up as 5:30:00 PM in F. I believe this is why the hrs arent calculating properly. As mentioned above, for the people using this to go into the formula bar to change AM to PM or what not, it would be too diffiuclt

Is there a way tojust have the 5:30 pull or if i enter just 5 for it to pull as 5:00. I created another column but cant seem to get the formula right. I tried LEFT(XX,5) but that doesnt seem to work

Thanks

**DGagnon** · 03-14-2012, 05:41 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.

**jame24** · 03-15-2012, 09:08 AM

Hey thanks. Last question, i promise

Updated the model to take that into account. Would you be able to just recreate the forumala for the Roster graph to take into account the HR, Min and AM/PM i created?

I have attached the model I'm working with nowLP and Roster Chart.xls

Thanks once again

**DGagnon** · 03-15-2012, 12:49 PM

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

**jame24** · 03-15-2012, 10:36 PM

can't seem to get the roster graph working...can you please take a look at it, keeping all other formulas as is?

thanks

**DGagnon** · 03-16-2012, 01:58 AM

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

**veejar** · 03-16-2012, 02:10 AM

Hi,

Can you try my timesheet and use similar formulas in your sheet

Regards,

Veejar

Calculating Start and End times

LinkBack

Thread Tools

Rate This Thread

Display

Calculating Start and End times

Re: help with Time Sheet and Roster

Re: help with Time Sheet and Roster

Re: help with Time Sheet and Roster

Re: help with Time Sheet and Roster

Re: help with Time Sheet and Roster

Re: help with Time Sheet and Roster

Re: help with Time Sheet and Roster

Re: help with Time Sheet and Roster

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Re: Calculating Start and End times

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions