Date and Time Calculations

**superclark** · 06-17-2009, 04:44 PM

Hi I am a contractor and i have employees on several jobs in an area so that means a employee can work from 6:00 AM to 9:00 AM on one job and then go to another job and work from 9:30 AM to 12:30 PM and so on. What i need to do is figure out how to make excel calculate hours on the first job and then calculate how many on the next job.
A B C
1 In Out Discription
2 6:00 AM 9:00 AM Worked on windows
3 9:30 AM 12:00 PM Installed new door knobs
4

Thanks

superclark

**Bozo** · 06-17-2009, 05:49 PM

Change the cell format in cell D2 to match that of cell C2 and then perform a simple formula in cell e2 being =D2-C2.

**superclark** · 06-18-2009, 08:03 PM

Yes that will just - 6 from 9 witch is 3. I need it to count how many hours it was from 6:00 AM to 9:30 AM etc. and then have it total each line.

Thanks

superclark

**DonkeyOte** · 06-19-2009, 02:36 AM

Your question isn't really clear ... first you said you wanted to track time on a specific job which Bozo interpreted as I did and answered ... now you seem to be saying you want to track time on a job which is based not on the end time of the job but the start of the next job if it exists ? Assuming that's the case then perhaps:

E2: =IF(C3,C3,D2)-C2

The result will be a time value, set format of E to hh:mm

If times cross midnight (unlikely) you can use use

E2: =MOD(IF(C3,C3,D2)-C2,1)

**superclark** · 06-20-2009, 12:39 PM

Hi,

I am sorry about my unclear question. I have spent a lot of time figuring this out but i did figure it out. I attached a blank copy of what i have built so you can see what i am saying. Fill free to use it if needed.

Thanks
Clark

**Bozo** · 06-20-2009, 04:44 PM

Glad you solved your issue. One small point. Look at your formula on the "Home" sheet, cell C7...suggest you use "=SUM('Day 1:Day 12'!D5)"....much neater!!