Help with time sheet

[Jason S.]

Hi all, Im trying to develop a time sheet log where it
counts down my extended contract work that I have done.
I've signed up for so many extra days of work a year and
it logs my hours and tells me how may hours I have left to
work. What I have does not seem to work, but is close.

I've signed up for 15 days extra, at 6hr's and 50min a
day. When I mark 15 in one cell, I have another cell
calculate the hours for me. It's set with this formula
=B7*TIME(6,50,0)
I think that works well. The formula is sitting in F10.

Then I log my time with the hours/minutes in the D
column. I have a separate cell that adds this all up with
the following formula.
=SUM(D10218)
There is no way I need 218 cells, but what the hay.
Anyway, right now should come up with 43 hours and 45
minutes. It's reporting 19:00. Something's up there.
This formula is sitting in F8.

Then I have a third cell that will take the hours I have
worked and subtract it form the hours I need to work.
This is formulated by:
=F10-F8
It comes up with a number that is close, but not exact. It
should be 58hours and 45 minutes. It shows 59 hours and
30 minutes. I do not even know how it is getting close
given the 19:00 that is reported above.

I have a feeling that my problem has something to do with
formatting of the cells. When I format it to time, it
more sets it for the time of day and not counting hours.
When I play with the formatting, far different numbers
come up with the above formulas. I get the closest
results when I customize the format and mark [h]:mm as the
formatting. Then the total time worked equals 43:00, but
nothing else changes.

Can someone point out what I am doing wrong?
Jason

 

[Trevor Shuttleworth]

Jason

for this part of the problem: "It's reporting 19:00", format the cell as
[hh]:mm

Regards

Trevor

 

[Jason S.]

I switched it, but it still seems to be 45 minutes off of
the true total I had put into. I noticed that as I enter
in the time each day I work, for example 1:45 for an hour
and fourty five minutes, it will show it as 12:45am in the
fx box at the top of excel.

Thanks for your help.
Jason

-----Original Message-----
Jason

for this part of the problem: "It's reporting 19:00", format the cell as
[hh]:mm

Regards

Trevor

Hi all, Im trying to develop a time sheet log where it
counts down my extended contract work that I have done.
I've signed up for so many extra days of work a year and
it logs my hours and tells me how may hours I have left to
work. What I have does not seem to work, but is close.

I've signed up for 15 days extra, at 6hr's and 50min a
day. When I mark 15 in one cell, I have another cell
calculate the hours for me. It's set with this formula
=B7*TIME(6,50,0)
I think that works well. The formula is sitting in F10.

Then I log my time with the hours/minutes in the D
column. I have a separate cell that adds this all up with
the following formula.
=SUM(D10218)
There is no way I need 218 cells, but what the hay.
Anyway, right now should come up with 43 hours and 45
minutes. It's reporting 19:00. Something's up there.
This formula is sitting in F8.

Then I have a third cell that will take the hours I have
worked and subtract it form the hours I need to work.
This is formulated by:
=F10-F8
It comes up with a number that is close, but not exact. It
should be 58hours and 45 minutes. It shows 59 hours and
30 minutes. I do not even know how it is getting close
given the 19:00 that is reported above.

I have a feeling that my problem has something to do with
formatting of the cells. When I format it to time, it
more sets it for the time of day and not counting hours.
When I play with the formatting, far different numbers
come up with the above formulas. I get the closest
results when I customize the format and mark [h]:mm as the
formatting. Then the total time worked equals 43:00, but
nothing else changes.

Can someone point out what I am doing wrong?
Jason

.

 

[Sandy Mann]

Jason,

If you re-format a cell in which you have entered 1:45 and it shows 12:45am
in the Formula Bar, as General, what do you get in the cell? If the true
contents of the cell was "1:45" then it should show 0.072916667, if it
really has "12:45am" as its contents then it should show 0.03125
Regards

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

I switched it, but it still seems to be 45 minutes off of
the true total I had put into. I noticed that as I enter
in the time each day I work, for example 1:45 for an hour
and fourty five minutes, it will show it as 12:45am in the
fx box at the top of excel.

Thanks for your help.
Jason

-----Original Message-----
Jason

for this part of the problem: "It's reporting 19:00", format the cell as
[hh]:mm

Regards

Trevor

Hi all, Im trying to develop a time sheet log where it
counts down my extended contract work that I have done.
I've signed up for so many extra days of work a year and
it logs my hours and tells me how may hours I have left to
work. What I have does not seem to work, but is close.

I've signed up for 15 days extra, at 6hr's and 50min a
day. When I mark 15 in one cell, I have another cell
calculate the hours for me. It's set with this formula
=B7*TIME(6,50,0)
I think that works well. The formula is sitting in F10.

Then I log my time with the hours/minutes in the D
column. I have a separate cell that adds this all up with
the following formula.
=SUM(D10218)
There is no way I need 218 cells, but what the hay.
Anyway, right now should come up with 43 hours and 45
minutes. It's reporting 19:00. Something's up there.
This formula is sitting in F8.

Then I have a third cell that will take the hours I have
worked and subtract it form the hours I need to work.
This is formulated by:
=F10-F8
It comes up with a number that is close, but not exact. It
should be 58hours and 45 minutes. It shows 59 hours and
30 minutes. I do not even know how it is getting close
given the 19:00 that is reported above.

I have a feeling that my problem has something to do with
formatting of the cells. When I format it to time, it
more sets it for the time of day and not counting hours.
When I play with the formatting, far different numbers
come up with the above formulas. I get the closest
results when I customize the format and mark [h]:mm as the
formatting. Then the total time worked equals 43:00, but
nothing else changes.

Can someone point out what I am doing wrong?
Jason

.

 

[roandr]

What if you try this;

Make a sheet where you write your start (A1) and end (A2) time of da
in two separate cells. Now use another cell (A3) with =(A2-A3)*24 an
format this cell as number. This will give you the exact amount o
hours you have worked that "day". Make another cell to sum up tha
column and you have the totals

 

[Tom Rinks]

Jason,

I do not know the answer to your formula problem but if you convert al
your days that you intend to work into hours at 6.833 per day you
formulas become more than simple.

Cheers,

To