Probs silly question...

  • Thread starter Thread starter chris turner
  • Start date Start date
C

chris turner

Hi all,

I have done a simple spread sheet that works out the hours I have worked in
hh:mm format. How do get a total sum calculation?

E.g. start 09:00 finish 16:35 =SUM(C3)-B3 daily hours 07:35

say I worked 5 days at 07:35 - I want the timesheet to caluclate the hours
and minutes worked.

Many thanks

Chris
 
Chris;

Do the calculations for each day.
Sum these results.
You would expect a total of say 50 hrs, but to see that you have to change
the cell-format.

So; format the cell with the total hrs to : [h]:mm:ss

This makes hours exceding 24 "visible".

Mark Rosenkrantz.

More Excel ? www.rosenkrantz.nl or (e-mail address removed)
 
Chris:

Here's what I have, column by column (in row 35, for example):
A35: Date
B35: [Time start, like 8:00 a], formatted as Custom h:mm AM/PM
C35: [Time end, like 5:00 p], formatted as Custom h:mm AM/PM
D35: =IF(C35>B35,C35-B35,24+C35-B35)
E35: =IF(B35="",0,IF(C35>B35,24*D35,24-(24-D35)*24))
F35: =F34+E35
G35: =E35*[hourly rate]
H35: =H34+G35

The more complicated formulas allow me to track a work session from 11 pm to
3 am without giving me an error. Don't ask me why. It just works.

Bruce Cooley



: Hi all,
:
: I have done a simple spread sheet that works out the hours I have worked
in
: hh:mm format. How do get a total sum calculation?
:
: E.g. start 09:00 finish 16:35 =SUM(C3)-B3 daily hours 07:35
:
: say I worked 5 days at 07:35 - I want the timesheet to caluclate the hours
: and minutes worked.
:
: Many thanks
:
: Chris
 
chris turner said:
Bruce Cooley said:
Chris:

Here's what I have, column by column (in row 35, for example):
A35: Date
B35: [Time start, like 8:00 a], formatted as Custom h:mm AM/PM
C35: [Time end, like 5:00 p], formatted as Custom h:mm AM/PM
D35: =IF(C35>B35,C35-B35,24+C35-B35)
E35: =IF(B35="",0,IF(C35>B35,24*D35,24-(24-D35)*24))
F35: =F34+E35
G35: =E35*[hourly rate]
H35: =H34+G35

The more complicated formulas allow me to track a work session from 11
Click to expand...
pm
to
3 am without giving me an error. Don't ask me why. It just works.

Bruce Cooley



: Hi all,
:
: I have done a simple spread sheet that works out the hours I have worked
in
: hh:mm format. How do get a total sum calculation?
:
: E.g. start 09:00 finish 16:35 =SUM(C3)-B3 daily hours 07:35
:
: say I worked 5 days at 07:35 - I want the timesheet to caluclate the hours
: and minutes worked.
:
: Many thanks
:
: Chris
Click to expand...
Many thanks - I will try out the suggestions and come back.

Chris
Click to expand...

Bruce - whats in cell f34 and h34?

cheers

chris
 
chris turner said:
chris turner said:
Bruce Cooley said:
Chris:

Here's what I have, column by column (in row 35, for example):
A35: Date
B35: [Time start, like 8:00 a], formatted as Custom h:mm AM/PM
C35: [Time end, like 5:00 p], formatted as Custom h:mm AM/PM
D35: =IF(C35>B35,C35-B35,24+C35-B35)
E35: =IF(B35="",0,IF(C35>B35,24*D35,24-(24-D35)*24))
F35: =F34+E35
G35: =E35*[hourly rate]
H35: =H34+G35

The more complicated formulas allow me to track a work session from 11
Click to expand...
pm
to
3 am without giving me an error. Don't ask me why. It just works.

Bruce Cooley



: Hi all,
:
: I have done a simple spread sheet that works out the hours I have worked
in
: hh:mm format. How do get a total sum calculation?
:
: E.g. start 09:00 finish 16:35 =SUM(C3)-B3 daily hours 07:35
:
: say I worked 5 days at 07:35 - I want the timesheet to caluclate the hours
: and minutes worked.
:
: Many thanks
:
: Chris
Click to expand...
Many thanks - I will try out the suggestions and come back.

Chris
Click to expand...

Bruce - whats in cell f34 and h34?

cheers

chris
Click to expand...

Bruce,

What I'm trying to do is add up hours worked e.g.

a3 7:35 (each formatted hh:mm)
b3 8:40 "
c3 8:20 "

Total : ???

Then I will muliply by hopurly rate etc.

Chris
 
I think you want this:

=sum(a3:b3)*24*(YourHourlyRate)

If you had 3 8 hour days (8:00), when you sum them, you'll get 24:00 (formatted
as [hh]:00). But if you format it as General, you'll see that it adds to 1.

Excel stores time as parts of a day. 8 hours = 1/3 day. 12 hours = 1/2 day.

So add up your hours, multiply by 24 to get the number of hours. then multiply
by your hourly rate.

Make sure you format the cell as General. Excel sometimes tries to help you by
formatting the cell with the formula as time.



chris turner wrote:
Bruce,

What I'm trying to do is add up hours worked e.g.

a3 7:35 (each formatted hh:mm)
b3 8:40 "
c3 8:20 "

Total : ???

Then I will muliply by hopurly rate etc.

Chris
Click to expand...
<<snipped>>
 
Chris,

F34 and H34, in my example, contain the previous row's data for calculating
cumulative hours worked and dollars earned. Here are the column headers:
A [Date]
B [Start]
C [Stop]
D Hours worked (hh:mm)
E Hours worked (decimal)
F Hours cumulative (decimal)
G Dollars earned in work session
H Dollars cumulative - earned since last invoice
I Task worked on

What this does is allow you to enter your start time and stop time on a
particular date, and it then calculates how much time you spent and how much
you earned, in that work session and cumulatively since the last time you
sent an invoice (in the row representing an invoice, you would zero out the
cumulative columns and continue down the spreadsheet with your next work
session).

A B C D E F G
H
15-Aug Invoice 0.00
16-Aug 12:00 PM 1:15 PM 1:15 1.25 1.25 $xx.xx $xxx.xx
1:45 PM 3:00 PM 1:15 1.25 2.50 $xx.xx $xxx.xx
4:30 PM 6:00 PM 1:30 1.50 4.00 $xx.xx $xxx.xx
17-Aug 2:00 AM 4:30 AM 2:30 2.50 6.50 $xxx.xx $xxx.xx
6:30 AM 7:30 AM 1:00 1.00 7.50 $xx.xx $xxx.xx
10:35 AM 12:15 PM 1:40 1.67 9.17 $xxx.xx $xxx.xx
1:30 PM 1:45 PM 0:15 0.25 9.42 $xx.xx $xxx.xx
etc.

Does that do what you want?

Bruce


: Bruce - whats in cell f34 and h34?
: chris


: > : > > Chris:
: > >
: > > Here's what I have, column by column (in row 35, for example):
: > > A35: Date
: > > B35: [Time start, like 8:00 a], formatted as Custom h:mm AM/PM
: > > C35: [Time end, like 5:00 p], formatted as Custom h:mm AM/PM
: > > D35: =IF(C35>B35,C35-B35,24+C35-B35)
: > > E35: =IF(B35="",0,IF(C35>B35,24*D35,24-(24-D35)*24))
: > > F35: =F34+E35
: > > G35: =E35*[hourly rate]
: > > H35: =H34+G35
: > >
: > > The more complicated formulas allow me to track a work session from 11
: pm
: > to
: > > 3 am without giving me an error. Don't ask me why. It just works.
: > >
: > > Bruce Cooley


: > > : > > : Hi all,
: > > :
: > > : I have done a simple spread sheet that works out the hours I have
: worked
: > > in
: > > : hh:mm format. How do get a total sum calculation?
: > > :
: > > : E.g. start 09:00 finish 16:35 =SUM(C3)-B3 daily hours 07:35
: > > :
: > > : say I worked 5 days at 07:35 - I want the timesheet to caluclate the
: > hours
: > > : and minutes worked.
: > > :
: > > : Many thanks
: > > :
: > > : Chris
 
Yes. A concise answer to the real question. Sorry about the diversions.

Bruce



: I think you want this:
:
: =sum(a3:b3)*24*(YourHourlyRate)
:
: If you had 3 8 hour days (8:00), when you sum them, you'll get 24:00
(formatted
: as [hh]:00). But if you format it as General, you'll see that it adds to
1.
:
: Excel stores time as parts of a day. 8 hours = 1/3 day. 12 hours = 1/2
day.
:
: So add up your hours, multiply by 24 to get the number of hours. then
multiply
: by your hourly rate.
:
: Make sure you format the cell as General. Excel sometimes tries to help
you by
: formatting the cell with the formula as time.
:
:
:
: chris turner wrote:
: <<snipped>>
: >
: > Bruce,
: >
: > What I'm trying to do is add up hours worked e.g.
: >
: > a3 7:35 (each formatted hh:mm)
: > b3 8:40 "
: > c3 8:20 "
: >
: > Total : ???
: >
: > Then I will muliply by hopurly rate etc.
: >
: > Chris
: <<snipped>>
: --
:
: Dave Peterson
: (e-mail address removed)
 
Back
Top