Excell formula

  eggy 21:51 04 Jun 04
Locked

I am trying to create a time sheet in excel but am having trouble working out the formula to subtract time and add times together. The sheet has the following format.

Date Day Starting Finishing Hours Lunch
Time Time attended Break

+
1/6 Mon 08.05 4.00 ------- -30 min



Hours Standard Daily Cumulative
Worked Day + -
------- 7.25


What is the formula I need for the dotted lines

  eggy 22:01 04 Jun 04

I am trying to create a time sheet in excel but am having trouble working out the formula to subtract time and add times together. The sheet has the following format.

Date Day Starting
Time Finishing Time Hours attended Lunch Break Hours worked Standard
Day Daily
+ - Cumulative
1/6 Mon 08.05 4.00 - 30 min 7.25
2/6 Tue
3/6 Wed
4/6 Thu


What is the formula I need for the dotted lines

  eggy 22:07 04 Jun 04

I am trying to create a time sheet in excel but am having trouble working out the formula to subtract time and add times together. Eg

Starting time 08.05am finishing time 4.00pm Hours attended = ------


What is the formula I need to work out the hours attended in excell

  eggy 22:09 04 Jun 04

ignore first two posts

  Tony uk 22:10 04 Jun 04

eggy you need to wait till VOG come in he's the main man on Excell

  FRANKMAC 22:10 04 Jun 04

This may help.

click here

  Simsy 22:50 04 Jun 04

you need to just subtract one from the other...

There are a couple of "secrets" when dealing with time...

1)Make sure the cells in question are formatted as "time". (If they are not, entering a "time" will usually automatically cause this formatting to be adopted in the cell, but it saves confusion if you change it beforehand)

2)When entering a "time", the time MUST be in the cell with a colon, (:), seperating the Hours:mins:secs... e.g in the example you gave 08:05 and 16:00, NOT 8.05 and 16.00 (Note that if you don't enter using the 24 hour format it makes things more complicated, but can be done)

If you follow the 2 conditions I've indicated above you will find that a simple subtraction of 16:00 - 08:05 will give the correct answer 08:25

For a fuller explanation use the link FRANKMAC has posted. It's where I learnt how to put a timesheet together. All the information is there.

If you do not have a shift crossing over midnight there are no complications... if you do it gets a bit more complicated, but, again, can faily easily be solved, see the site indicated...

Good luck,

Regards,

Simsy

  THE TERMINATOR 00:44 05 Jun 04

first you need to format the cells you are going to use. Highlight the cells(A1 to C6) and right click the mouse and choose formatcells. Choose Time and in the pull down box choose English(united states)and choose13:30.
in A1 put 08:05 in B1 put 16:00 and in C1 put=b1-a1. This can only work if you use 24HR time
TT

  THE TERMINATOR 00:50 05 Jun 04

Row C should show the hours worked ie time start=08:05 time finish=16:00 total=07:55 HRS worked. Hope this helps TT

  Cook2 02:10 05 Jun 04

As above plus a little tip when finishing after midnight.

If the finishing time is say 01:00 then add 24 and enter as 25:00.

  Cook2 02:25 05 Jun 04

And make sure you use the Format 37:30:15 in the 'Totals' cells.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…