Excel Timesheet

[Guest]

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

 

[Bob Phillips]

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)

 

[Anne Troy]

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

 

[Guest]

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

 

[Paul Sheppard]

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

Many thanks

Matt

Try formatting the cell as [h]:m

 

[Guest]

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

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

Matt

Try formatting the cell as [h]:mm

 

[Sandy Mann]

Paul gave you the answer yesterday:

Matt

Try formatting the cell as [h]:mm


--
Paul Sheppard




--
Regards


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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

:

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)


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?action=getinfo&userid=24783
View this thread:
http://www.excelforum.com/showthread.php?threadid=401464

 

[Guest]

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

Paul gave you the answer yesterday:

Matt

Try formatting the cell as [h]:mm


--
Paul Sheppard




--
Regards


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

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

:

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)


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?action=getinfo&userid=24783
View this thread:
http://www.excelforum.com/showthread.php?threadid=401464

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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

Paul gave you the answer yesterday:

Matt

Try formatting the cell as [h]:mm


--
Paul Sheppard




--
Regards


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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






:


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

:

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)


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?action=getinfo&userid=24783
View this thread:
http://www.excelforum.com/showthread.php?threadid=401464