time clock formulas

[Guest]

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can
not get those numbers in the C colum to add up for a rolling total

HELP !!!!!

 

[Guest]

Try formatting the sum as;

[h]:mm

 

[Guest]

I'm not sure what you mean, sorry...

Try formatting the sum as;

[h]:mm
--
Gary's Student

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can
not get those numbers in the C colum to add up for a rolling total

HELP !!!!!

 

[Bob Phillips]

As they are just decimal numbers now, they should simply add up. What is the
problem you get, give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I'm not sure what you mean, sorry...

Try formatting the sum as;

[h]:mm
--
Gary's Student

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can
not get those numbers in the C colum to add up for a rolling total

HELP !!!!!

 

[Peo Sjoblom]

Maybe Andrew meant something like this in C2 so the values accumulate

=(B2-A2)*24+C1

and copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

As they are just decimal numbers now, they should simply add up. What is
the
problem you get, give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I'm not sure what you mean, sorry...

Try formatting the sum as;

[h]:mm
--
Gary's Student


:

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can
not get those numbers in the C colum to add up for a rolling total

HELP !!!!!

 

[Guest]

I need each row to have its own total, and then have a total at the bottem of
each column.

Maybe Andrew meant something like this in C2 so the values accumulate

=(B2-A2)*24+C1

and copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

As they are just decimal numbers now, they should simply add up. What is
the
problem you get, give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I'm not sure what you mean, sorry...

:

Try formatting the sum as;

[h]:mm
--
Gary's Student


:

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can
not get those numbers in the C colum to add up for a rolling total

HELP !!!!!

 

[Peo Sjoblom]

Just use =SUM(C1:C?)

where C? is the last cell with data that you want to include

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

I need each row to have its own total, and then have a total at the bottem
of
each column.

Maybe Andrew meant something like this in C2 so the values accumulate

=(B2-A2)*24+C1

and copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

As they are just decimal numbers now, they should simply add up. What
is
the
problem you get, give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I'm not sure what you mean, sorry...

:

Try formatting the sum as;

[h]:mm
--
Gary's Student


:

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great,
however I
can
not get those numbers in the C colum to add up for a rolling total

HELP !!!!!

 

[Guest]

I know that, but t does not work
a b c
1 7:30 17:30 10.00
2 7:30 17:30 10.00
3 7:30 10:30 3.00
4 7:15 17:30 10.25
5 7:15 17:30 10.25

#VALUE!


Formula for c3 is =(b1-a1)*24

hope ths helps

Just use =SUM(C1:C?)

where C? is the last cell with data that you want to include

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

I need each row to have its own total, and then have a total at the bottem
of
each column.

Maybe Andrew meant something like this in C2 so the values accumulate

=(B2-A2)*24+C1

and copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




As they are just decimal numbers now, they should simply add up. What
is
the
problem you get, give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I'm not sure what you mean, sorry...

:

Try formatting the sum as;

[h]:mm
--
Gary's Student


:

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great,
however I
can
not get those numbers in the C colum to add up for a rolling total

HELP !!!!!

 

[Peo Sjoblom]

Are you saying that if you use

=SUM(C1:C5)

you'll get a value error?



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

I know that, but t does not work
a b c
1 7:30 17:30 10.00
2 7:30 17:30 10.00
3 7:30 10:30 3.00
4 7:15 17:30 10.25
5 7:15 17:30 10.25

#VALUE!


Formula for c3 is =(b1-a1)*24

hope ths helps

Just use =SUM(C1:C?)

where C? is the last cell with data that you want to include

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

I need each row to have its own total, and then have a total at the
bottem
of
each column.

:

Maybe Andrew meant something like this in C2 so the values accumulate

=(B2-A2)*24+C1

and copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




As they are just decimal numbers now, they should simply add up.
What
is
the
problem you get, give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I'm not sure what you mean, sorry...

:

Try formatting the sum as;

[h]:mm
--
Gary's Student


:

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great,
however I
can
not get those numbers in the C colum to add up for a rolling
total

HELP !!!!!

 

[Guest]

it is easy Andrew.pse find below copy of excel sheet example;copy it directly
into a sheet.
start time ending time working time
hours minutes
12:23 14:42 2,00 19 2:19
14:41 14:42 0,00 1 0:01
12:12 14:42 2,00 30 2:30
11:23 14:42 3,00 19 3:19

totals: 7,00 69,00 8 uur en 9 minuten solution a)
8:09 solution b)
8:09 solution c)

formula's were written in dutch as you can see below (relevant cellcontents
preceeded by an apostrophy)

12:23:00 14:42:00 =UUR(B14-A14) =MINUUT(B14-A14) =TIJD(UUR(B14-A14);MINUUT(B14-A14);SECONDE(I14-J14))
14:41:00 14:42:00 =UUR(B15-A15) =MINUUT(B15-A15) =TIJD(UUR(B15-A15);MINUUT(B15-A15);SECONDE(I15-J15))
12:12 14:42 2,00 30 2:30
11:23 14:42 3,00 19 3:19

totaal: =SOM(D1417) =SOM(E14:E17) =TEKST.SAMENVOEGEN((D19+INTEGER(E19/60))&" uur en ";REST(E19;60)&" minuten")
=TIJD(D19;E19;F19)
=SOM(G14:G17)
Believe required English formula version as follows:

hour minute concatenate integer rest(or remainder??)
sum sum time
second

 

[Guest]

That's correct

Are you saying that if you use

=SUM(C1:C5)

you'll get a value error?



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

I know that, but t does not work
a b c
1 7:30 17:30 10.00
2 7:30 17:30 10.00
3 7:30 10:30 3.00
4 7:15 17:30 10.25
5 7:15 17:30 10.25

#VALUE!


Formula for c3 is =(b1-a1)*24

hope ths helps

Just use =SUM(C1:C?)

where C? is the last cell with data that you want to include

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




I need each row to have its own total, and then have a total at the
bottem
of
each column.

:

Maybe Andrew meant something like this in C2 so the values accumulate

=(B2-A2)*24+C1

and copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




As they are just decimal numbers now, they should simply add up.
What
is
the
problem you get, give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I'm not sure what you mean, sorry...

:

Try formatting the sum as;

[h]:mm
--
Gary's Student


:

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great,
however I
can
not get those numbers in the C colum to add up for a rolling
total

HELP !!!!!

 

[Peo Sjoblom]

The only way you can get a value error is if you calculate something seen as
text and if C1:C5 are all numbers then you can not get an error, if any
value in A1:A5 or B1:B5 are text then you can get a value error in C1:C5 and
if you get one there then the sum will return the error as well, what
happens if you use

=SUMIF(C1:C5,"<>#VALUE!")

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

That's correct

Are you saying that if you use

=SUM(C1:C5)

you'll get a value error?



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

I know that, but t does not work
a b c
1 7:30 17:30 10.00
2 7:30 17:30 10.00
3 7:30 10:30 3.00
4 7:15 17:30 10.25
5 7:15 17:30 10.25

#VALUE!


Formula for c3 is =(b1-a1)*24

hope ths helps

:

Just use =SUM(C1:C?)

where C? is the last cell with data that you want to include

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




I need each row to have its own total, and then have a total at the
bottem
of
each column.

:

Maybe Andrew meant something like this in C2 so the values
accumulate

=(B2-A2)*24+C1

and copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




As they are just decimal numbers now, they should simply add up.
What
is
the
problem you get, give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I'm not sure what you mean, sorry...

:

Try formatting the sum as;

[h]:mm
--
Gary's Student


:

I have created a formula for calculating total hours / day
the times are entered in military time
a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great,
however I
can
not get those numbers in the C colum to add up for a rolling
total

HELP !!!!!

 

[protonLeah]

Assuming that the first seven rows of column A hold the day names, i.e.,
Mon, Tues, etc. then:

- The first seven cells in columns B, C & D should be formatted for
24 hour time (13:30)
- The first seven cells in column D would contain the formula:
=IF(C1>B1,SUM(C1-B1),SUM(C1+24-B1)) "time worked in hrs and
mins"
- The cells of column E would be formatted as number, say 1 decimal
place, and contain the formula:
=SUM(HOUR(D1),(MINUTE(D1)/60)) "time worked in hours and
tenths
- Cell E8 would simply sum E1:E7 for the weekly total in hours and
tenths.

 

[Guest]

I've have the same thing. I have built a spreedsheet to kept track of my
hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get
the spreedsheet to total the hours. Every outher week I have to work over, I
go in
at 21:45, but I don't get off until 14:00 the next day, for a total of
16:15. I NEED
HELP WITH THIS ONE PLEASE.

 

[Daniel CHEN]

=(B1-A1)*24+24*IF(B1>A1,0,1)

--
Best regards,
---
Yongjun CHEN
==================================
- - - - www.XLDataSoft.com - - - -
Free Excel/VBA Tool & Training Material
==================================

 

[Roger Govier]

Hi Ron

With clock on in A1 and clock off in B1 try
=MOD(B1-A1,1)

 

[Guest]

Roger, I would like to email you my time sheet so you can see if you can
help. I need your email address or a place where I can send this. Ron
(IRONMN)

 

[Roger Govier]

Hi Ron

Send the file to roger dot govier at technology4u dot co dot uk

Do the obvious things with dot and at

 

[Guest]

Roger, Hi, i was just checking to see if you got it. Ron

 

[Roger Govier]

Hi Ron

File received and on its way back.
Your problem was not in the calculation of time from start to finish.
Excel stores times as fractions of a day, so before you multiply the
hours worked by the dollar rate, you need to multiply by 24 (hours per
day) to convert to decimal hours.
In order for the correct result to display, the cell with this
calculation needs to be formatted as General or Number (not Time).