Excel Timesheet

Hi all

Im trying to set up a new timesheet for use at work but dont know much
about formulas etc, i need to put a start and finish time e.g. start 8.30,
finish 12.30, then i need to calculate in a column at the end telling me how
many
hours ive worked between 8.30 and 12.30 then the same for the afternoon
from 1.30 until 5pm with another column at the ending adding the hours worked
in the morning to the hours worked in the afternoon giving me total hours
worked that day and the same everyday for the week with a box at the bottom
giving me total hours worked for the week, please help.
Thanks

Just subtract the lower from the higher, and format that cell as time as
well

=B2-A2

then just add the two totals cells together, and again format as time, say

=E2+F2

then at the bottom, just sum them and format as time

=SUM(G2:G8)

If you want a decimal number, say to multiply by rate to get pay, multiply
by 24

=G10*24


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matt" <Matt@discussions.microsoft.com> wrote in message
news:A1841F78-7E3C-48A0-BAD6-3B721EDAA58C@microsoft.com...
> Hi all
>
> Im trying to set up a new timesheet for use at work but dont know much
> about formulas etc, i need to put a start and finish time e.g. start 8.30,
> finish 12.30, then i need to calculate in a column at the end telling me
how
> many
> hours ive worked between 8.30 and 12.30 then the same for the afternoon
> from 1.30 until 5pm with another column at the ending adding the hours
worked
> in the morning to the hours worked in the afternoon giving me total hours
> worked that day and the same everyday for the week with a box at the
bottom
> giving me total hours worked for the week, please help.
> Thanks
>

Matt, try Chip Pearson's site:
http://www.cpearson.com/excel/datetime.htm#TOC
************
Anne Troy
www.OfficeArticles.com

"Matt" <Matt@discussions.microsoft.com> wrote in message
news:A1841F78-7E3C-48A0-BAD6-3B721EDAA58C@microsoft.com...
> Hi all
>
> Im trying to set up a new timesheet for use at work but dont know much
> about formulas etc, i need to put a start and finish time e.g. start 8.30,
> finish 12.30, then i need to calculate in a column at the end telling me
> how
> many
> hours ive worked between 8.30 and 12.30 then the same for the afternoon
> from 1.30 until 5pm with another column at the ending adding the hours
> worked
> in the morning to the hours worked in the afternoon giving me total hours
> worked that day and the same everyday for the week with a box at the
> bottom
> giving me total hours worked for the week, please help.
> Thanks
>

I can now get the start and finish times to add up the hours i have done each
day but at the bottom when i go to add up the total hours worked in the week
as it is set to time format when you get to 24:00 it goes back to 01:00
instead of carrying on as i need the total hours to read at least 37.5 hours,
how can i solve this?

Many thanks

"Bob Phillips" wrote:

> Just subtract the lower from the higher, and format that cell as time as
> well
>
> =B2-A2
>
> then just add the two totals cells together, and again format as time, say
>
> =E2+F2
>
> then at the bottom, just sum them and format as time
>
> =SUM(G2:G8)
>
> If you want a decimal number, say to multiply by rate to get pay, multiply
> by 24
>
> =G10*24
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:A1841F78-7E3C-48A0-BAD6-3B721EDAA58C@microsoft.com...
> > Hi all
> >
> > Im trying to set up a new timesheet for use at work but dont know much
> > about formulas etc, i need to put a start and finish time e.g. start 8.30,
> > finish 12.30, then i need to calculate in a column at the end telling me
> how
> > many
> > hours ive worked between 8.30 and 12.30 then the same for the afternoon
> > from 1.30 until 5pm with another column at the ending adding the hours
> worked
> > in the morning to the hours worked in the afternoon giving me total hours
> > worked that day and the same everyday for the week with a box at the
> bottom
> > giving me total hours worked for the week, please help.
> > Thanks
> >
>
>
>

I am sorry people are not understanding. I have got my spreadsheet together
so at the end of each day it adds up how many hours ive worked during each
day e.g. see below:

Day Start Finish Start Finish Hours
Mon 7:50 13:00 5:10 14:00 17:00 3:00 8:10
Tue 8:45 13:00 4:15 13:45 17:15 3:30 7:45
Wed 8:40 13:00 4:20 13:45 17:20 3:35 7:55
Thur 8:40 13:00 4:20 13:45 17:20 3:35 7:55
Fri 8:00 13:00 5:00 13:45 18:00 4:15 9:15
Total 17:00

when i go to add up the total hours worked during the ween at the bottom it
still uses 24 hr clock and when it gets to 24:00 it resets to 01:00 or
something instead of carrying on, i also need it so it just counts
1-60seconds in time format not up to 100 in normal number.

Please help someone.

Thanks






"Paul Sheppard" wrote:

>
> Matt Wrote:
> > I can now get the start and finish times to add up the hours i have done
> > each
> > day but at the bottom when i go to add up the total hours worked in the
> > week
> > as it is set to time format when you get to 24:00 it goes back to
> > 01:00
> > instead of carrying on as i need the total hours to read at least 37.5
> > hours,
> > how can i solve this?
> >
> > Many thanks
> >
> > "Bob Phillips" wrote:
> >
> > > Just subtract the lower from the higher, and format that cell as time
> > as
> > > well
> > >
> > > =B2-A2
> > >
> > > then just add the two totals cells together, and again format as
> > time, say
> > >
> > > =E2+F2
> > >
> > > then at the bottom, just sum them and format as time
> > >
> > > =SUM(G2:G8)
> > >
> > > If you want a decimal number, say to multiply by rate to get pay,
> > multiply
> > > by 24
> > >
> > > =G10*24
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Matt" <Matt@discussions.microsoft.com> wrote in message
> > > news:A1841F78-7E3C-48A0-BAD6-3B721EDAA58C@microsoft.com...
> > > > Hi all
> > > >
> > > > Im trying to set up a new timesheet for use at work but dont know
> > much
> > > > about formulas etc, i need to put a start and finish time e.g.
> > start 8.30,
> > > > finish 12.30, then i need to calculate in a column at the end
> > telling me
> > > how
> > > > many
> > > > hours ive worked between 8.30 and 12.30 then the same for the
> > afternoon
> > > > from 1.30 until 5pm with another column at the ending adding the
> > hours
> > > worked
> > > > in the morning to the hours worked in the afternoon giving me total
> > hours
> > > > worked that day and the same everyday for the week with a box at
> > the
> > > bottom
> > > > giving me total hours worked for the week, please help.
> > > > Thanks
> > > >
> > >
> > >
> > >
>
> Matt
>
> Try formatting the cell as [h]:mm
>
>
> --
> Paul Sheppard
>
>
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=401464
>
>

Paul gave you the answer yesterday:

Matt

Try formatting the cell as [h]:mm


--
Paul Sheppard




--
Regards


Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Matt" <Matt@discussions.microsoft.com> wrote in message
news:D9E7F04E-2BB7-4FBC-866C-BA04373B8E62@microsoft.com...
>I am sorry people are not understanding. I have got my spreadsheet
>together
> so at the end of each day it adds up how many hours ive worked during each
> day e.g. see below:
>
> Day Start Finish Start Finish Hours
> Mon 7:50 13:00 5:10 14:00 17:00 3:00 8:10
> Tue 8:45 13:00 4:15 13:45 17:15 3:30 7:45
> Wed 8:40 13:00 4:20 13:45 17:20 3:35 7:55
> Thur 8:40 13:00 4:20 13:45 17:20 3:35 7:55
> Fri 8:00 13:00 5:00 13:45 18:00 4:15 9:15
> Total 17:00
>
> when i go to add up the total hours worked during the ween at the bottom
> it
> still uses 24 hr clock and when it gets to 24:00 it resets to 01:00 or
> something instead of carrying on, i also need it so it just counts
> 1-60seconds in time format not up to 100 in normal number.
>
> Please help someone.
>
> Thanks
>
>
>
>
>
>
> "Paul Sheppard" wrote:
>
>>
>> Matt Wrote:
>> > I can now get the start and finish times to add up the hours i have
>> > done
>> > each
>> > day but at the bottom when i go to add up the total hours worked in the
>> > week
>> > as it is set to time format when you get to 24:00 it goes back to
>> > 01:00
>> > instead of carrying on as i need the total hours to read at least 37.5
>> > hours,
>> > how can i solve this?
>> >
>> > Many thanks
>> >
>> > "Bob Phillips" wrote:
>> >
>> > > Just subtract the lower from the higher, and format that cell as time
>> > as
>> > > well
>> > >
>> > > =B2-A2
>> > >
>> > > then just add the two totals cells together, and again format as
>> > time, say
>> > >
>> > > =E2+F2
>> > >
>> > > then at the bottom, just sum them and format as time
>> > >
>> > > =SUM(G2:G8)
>> > >
>> > > If you want a decimal number, say to multiply by rate to get pay,
>> > multiply
>> > > by 24
>> > >
>> > > =G10*24
>> > >
>> > >
>> > > --
>> > >
>> > > HTH
>> > >
>> > > RP
>> > > (remove nothere from the email address if mailing direct)
>> > >
>> > >
>> > > "Matt" <Matt@discussions.microsoft.com> wrote in message
>> > > news:A1841F78-7E3C-48A0-BAD6-3B721EDAA58C@microsoft.com...
>> > > > Hi all
>> > > >
>> > > > Im trying to set up a new timesheet for use at work but dont know
>> > much
>> > > > about formulas etc, i need to put a start and finish time e.g.
>> > start 8.30,
>> > > > finish 12.30, then i need to calculate in a column at the end
>> > telling me
>> > > how
>> > > > many
>> > > > hours ive worked between 8.30 and 12.30 then the same for the
>> > afternoon
>> > > > from 1.30 until 5pm with another column at the ending adding the
>> > hours
>> > > worked
>> > > > in the morning to the hours worked in the afternoon giving me total
>> > hours
>> > > > worked that day and the same everyday for the week with a box at
>> > the
>> > > bottom
>> > > > giving me total hours worked for the week, please help.
>> > > > Thanks
>> > > >
>> > >
>> > >
>> > >
>>
>> Matt
>>
>> Try formatting the cell as [h]:mm
>>
>>
>> --
>> Paul Sheppard
>>
>>
>> ------------------------------------------------------------------------
>> Paul Sheppard's Profile:
>> http://www.excelforum.com/member.php...o&userid=24783
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=401464
>>
>>

Ok i have got it set up now and i can work out my overtime as flexi time etc
but i did under the standard hours i am supposed to do in a week and it gave
me a load of hash symbols, how do i format the time to give a negative answer
e.g. -01:30.

Thanks

"Sandy Mann" wrote:

> Paul gave you the answer yesterday:
>
> Matt
>
> Try formatting the cell as [h]:mm
>
>
> --
> Paul Sheppard
>
>
>
>
> --
> Regards
>
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:D9E7F04E-2BB7-4FBC-866C-BA04373B8E62@microsoft.com...
> >I am sorry people are not understanding. I have got my spreadsheet
> >together
> > so at the end of each day it adds up how many hours ive worked during each
> > day e.g. see below:
> >
> > Day Start Finish Start Finish Hours
> > Mon 7:50 13:00 5:10 14:00 17:00 3:00 8:10
> > Tue 8:45 13:00 4:15 13:45 17:15 3:30 7:45
> > Wed 8:40 13:00 4:20 13:45 17:20 3:35 7:55
> > Thur 8:40 13:00 4:20 13:45 17:20 3:35 7:55
> > Fri 8:00 13:00 5:00 13:45 18:00 4:15 9:15
> > Total 17:00
> >
> > when i go to add up the total hours worked during the ween at the bottom
> > it
> > still uses 24 hr clock and when it gets to 24:00 it resets to 01:00 or
> > something instead of carrying on, i also need it so it just counts
> > 1-60seconds in time format not up to 100 in normal number.
> >
> > Please help someone.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
> > "Paul Sheppard" wrote:
> >
> >>
> >> Matt Wrote:
> >> > I can now get the start and finish times to add up the hours i have
> >> > done
> >> > each
> >> > day but at the bottom when i go to add up the total hours worked in the
> >> > week
> >> > as it is set to time format when you get to 24:00 it goes back to
> >> > 01:00
> >> > instead of carrying on as i need the total hours to read at least 37.5
> >> > hours,
> >> > how can i solve this?
> >> >
> >> > Many thanks
> >> >
> >> > "Bob Phillips" wrote:
> >> >
> >> > > Just subtract the lower from the higher, and format that cell as time
> >> > as
> >> > > well
> >> > >
> >> > > =B2-A2
> >> > >
> >> > > then just add the two totals cells together, and again format as
> >> > time, say
> >> > >
> >> > > =E2+F2
> >> > >
> >> > > then at the bottom, just sum them and format as time
> >> > >
> >> > > =SUM(G2:G8)
> >> > >
> >> > > If you want a decimal number, say to multiply by rate to get pay,
> >> > multiply
> >> > > by 24
> >> > >
> >> > > =G10*24
> >> > >
> >> > >
> >> > > --
> >> > >
> >> > > HTH
> >> > >
> >> > > RP
> >> > > (remove nothere from the email address if mailing direct)
> >> > >
> >> > >
> >> > > "Matt" <Matt@discussions.microsoft.com> wrote in message
> >> > > news:A1841F78-7E3C-48A0-BAD6-3B721EDAA58C@microsoft.com...
> >> > > > Hi all
> >> > > >
> >> > > > Im trying to set up a new timesheet for use at work but dont know
> >> > much
> >> > > > about formulas etc, i need to put a start and finish time e.g.
> >> > start 8.30,
> >> > > > finish 12.30, then i need to calculate in a column at the end
> >> > telling me
> >> > > how
> >> > > > many
> >> > > > hours ive worked between 8.30 and 12.30 then the same for the
> >> > afternoon
> >> > > > from 1.30 until 5pm with another column at the ending adding the
> >> > hours
> >> > > worked
> >> > > > in the morning to the hours worked in the afternoon giving me total
> >> > hours
> >> > > > worked that day and the same everyday for the week with a box at
> >> > the
> >> > > bottom
> >> > > > giving me total hours worked for the week, please help.
> >> > > > Thanks
> >> > > >
> >> > >
> >> > >
> >> > >
> >>
> >> Matt
> >>
> >> Try formatting the cell as [h]:mm
> >>
> >>
> >> --
> >> Paul Sheppard
> >>
> >>
> >> ------------------------------------------------------------------------
> >> Paul Sheppard's Profile:
> >> http://www.excelforum.com/member.php...o&userid=24783
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=401464
> >>
> >>
>
>
>

XL normally cannot show negative times, (it can calculate them it just can't
display them). To show negative time change to the 1904 date system:

Tools > Options > Calculation > 1904 date system

But note that ALL dates in that workbook will change by four years and one
day.

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Matt" <Matt@discussions.microsoft.com> wrote in message
news:ECA68029-4283-40A3-931C-47A4AEE1DFB0@microsoft.com...
> Ok i have got it set up now and i can work out my overtime as flexi time
> etc
> but i did under the standard hours i am supposed to do in a week and it
> gave
> me a load of hash symbols, how do i format the time to give a negative
> answer
> e.g. -01:30.
>
> Thanks
>
> "Sandy Mann" wrote:
>
>> Paul gave you the answer yesterday:
>>
>> Matt
>>
>> Try formatting the cell as [h]:mm
>>
>>
>> --
>> Paul Sheppard
>>
>>
>>
>>
>> --
>> Regards
>>
>>
>> Sandy
>> sandymann@mailinator.com
>> Replace@mailinator with @tiscali.co.uk
>>
>>
>> "Matt" <Matt@discussions.microsoft.com> wrote in message
>> news:D9E7F04E-2BB7-4FBC-866C-BA04373B8E62@microsoft.com...
>> >I am sorry people are not understanding. I have got my spreadsheet
>> >together
>> > so at the end of each day it adds up how many hours ive worked during
>> > each
>> > day e.g. see below:
>> >
>> > Day Start Finish Start Finish Hours
>> > Mon 7:50 13:00 5:10 14:00 17:00 3:00 8:10
>> > Tue 8:45 13:00 4:15 13:45 17:15 3:30 7:45
>> > Wed 8:40 13:00 4:20 13:45 17:20 3:35 7:55
>> > Thur 8:40 13:00 4:20 13:45 17:20 3:35 7:55
>> > Fri 8:00 13:00 5:00 13:45 18:00 4:15 9:15
>> > Total 17:00
>> >
>> > when i go to add up the total hours worked during the ween at the
>> > bottom
>> > it
>> > still uses 24 hr clock and when it gets to 24:00 it resets to 01:00 or
>> > something instead of carrying on, i also need it so it just counts
>> > 1-60seconds in time format not up to 100 in normal number.
>> >
>> > Please help someone.
>> >
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Paul Sheppard" wrote:
>> >
>> >>
>> >> Matt Wrote:
>> >> > I can now get the start and finish times to add up the hours i have
>> >> > done
>> >> > each
>> >> > day but at the bottom when i go to add up the total hours worked in
>> >> > the
>> >> > week
>> >> > as it is set to time format when you get to 24:00 it goes back to
>> >> > 01:00
>> >> > instead of carrying on as i need the total hours to read at least
>> >> > 37.5
>> >> > hours,
>> >> > how can i solve this?
>> >> >
>> >> > Many thanks
>> >> >
>> >> > "Bob Phillips" wrote:
>> >> >
>> >> > > Just subtract the lower from the higher, and format that cell as
>> >> > > time
>> >> > as
>> >> > > well
>> >> > >
>> >> > > =B2-A2
>> >> > >
>> >> > > then just add the two totals cells together, and again format as
>> >> > time, say
>> >> > >
>> >> > > =E2+F2
>> >> > >
>> >> > > then at the bottom, just sum them and format as time
>> >> > >
>> >> > > =SUM(G2:G8)
>> >> > >
>> >> > > If you want a decimal number, say to multiply by rate to get pay,
>> >> > multiply
>> >> > > by 24
>> >> > >
>> >> > > =G10*24
>> >> > >
>> >> > >
>> >> > > --
>> >> > >
>> >> > > HTH
>> >> > >
>> >> > > RP
>> >> > > (remove nothere from the email address if mailing direct)
>> >> > >
>> >> > >
>> >> > > "Matt" <Matt@discussions.microsoft.com> wrote in message
>> >> > > news:A1841F78-7E3C-48A0-BAD6-3B721EDAA58C@microsoft.com...
>> >> > > > Hi all
>> >> > > >
>> >> > > > Im trying to set up a new timesheet for use at work but dont
>> >> > > > know
>> >> > much
>> >> > > > about formulas etc, i need to put a start and finish time e.g.
>> >> > start 8.30,
>> >> > > > finish 12.30, then i need to calculate in a column at the end
>> >> > telling me
>> >> > > how
>> >> > > > many
>> >> > > > hours ive worked between 8.30 and 12.30 then the same for the
>> >> > afternoon
>> >> > > > from 1.30 until 5pm with another column at the ending adding the
>> >> > hours
>> >> > > worked
>> >> > > > in the morning to the hours worked in the afternoon giving me
>> >> > > > total
>> >> > hours
>> >> > > > worked that day and the same everyday for the week with a box at
>> >> > the
>> >> > > bottom
>> >> > > > giving me total hours worked for the week, please help.
>> >> > > > Thanks
>> >> > > >
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >> Matt
>> >>
>> >> Try formatting the cell as [h]:mm
>> >>
>> >>
>> >> --
>> >> Paul Sheppard
>> >>
>> >>
>> >> ------------------------------------------------------------------------
>> >> Paul Sheppard's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=24783
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=401464
>> >>
>> >>
>>
>>
>>