XL 2007

Iwant to have a 'simple' timesheet where a user types in their start
time, end time, lunch break etc and XL calcs the total number of hours
they've worked. And calcs against their contracted hours, to see if
they are in time credit or edbit.

The problem seems to be if they have worked less than their contracted
hours, XL displays #####'s if there's a negative number.

I realise it's something to do with the end time being before the
start time, and the 24-hour clock etc, but I can get so very close,
but not get it perfect. For example, my latest version does everyting
I want apart from dosplaying the 'Excess' (time they're in
credit/debit) as a negative if they've worked less hours. This then
screws-up the running total.

Help! Please!

Steve


start time end time total start lunch end lunch
lunch total Grand Total Excess R/Total

After you put the formula in the cell, change the Cell's format to General
and you should see the negative values you want.

--
Rick (MVP - Excel)


"Steve" wrote in message ...
XL 2007

Iwant to have a 'simple' timesheet where a user types in their start
time, end time, lunch break etc and XL calcs the total number of hours
they've worked. And calcs against their contracted hours, to see if
they are in time credit or edbit.

The problem seems to be if they have worked less than their contracted
hours, XL displays #####'s if there's a negative number.

I realise it's something to do with the end time being before the
start time, and the 24-hour clock etc, but I can get so very close,
but not get it perfect. For example, my latest version does everyting
I want apart from dosplaying the 'Excess' (time they're in
credit/debit) as a negative if they've worked less hours. This then
screws-up the running total.

Help! Please!

Steve


start time end time total start lunch end lunch
lunch total Grand Total Excess R/Total

Change the "Excess" time to display a number of hours instead of a
time format. Negative time formats won't work.
The formula in "Excess" would be something like:
=((end time)-(start time)-[(end lunch)-(start lunch)])*24
Then change the format for this cell to "General" or "0.00".

Hi Rick

thanks for the quick response.

I tried it, but it gave the result -0.1666667 - so we're sort-of
there, but I want the result to show -4,so to show I worked 4 hours
less than I was contracted to.

Any more ideas?

Steve



On Tue, 24 Mar 2009 14:57:51 -0400, "Rick Rothstein"
wrote:

After you put the formula in the cell, change the Cell's format to General
and you should see the negative values you want.

Multiply the subtraction by 24 (the number of hours in a day... time values
are decimals that are a fraction of a full day). For example...

=24*(A1-B1)

--
Rick (MVP - Excel)


"Steve" wrote in message ...
Hi Rick

thanks for the quick response.

I tried it, but it gave the result -0.1666667 - so we're sort-of
there, but I want the result to show -4,so to show I worked 4 hours
less than I was contracted to.

Any more ideas?

Steve



On Tue, 24 Mar 2009 14:57:51 -0400, "Rick Rothstein"
wrote:

After you put the formula in the cell, change the Cell's format to General
and you should see the negative values you want.

Hi Jason

Thanks for this. Not quite worked.

I tried it and have:

start time end time total1 start lunch end lunch total2 Grand Total Excess R/Total
08:00:00 17:00:00 09:00:00 13:00:00 13:30:00 00:30:00 08:30:00 -8.50 -8.19

so total1 is the hours worked

total2 is the total lunch break time

I'm subtracting total2 from total1 to get Grand Total

excess should be 07:30 (the daily contracted hours) minus Grand total

and R/total would be a running total for the week.

I hope this is now a bit clearer.

I'm off out for the night now, but will check this out tomorrow and try again.

Thanks again, much appreciated

Steve



On Tue, 24 Mar 2009 12:04:56 -0700 (PDT), jasontferrell wrote:

Change the "Excess" time to display a number of hours instead of a
time format. Negative time formats won't work.
The formula in "Excess" would be something like:
=((end time)-(start time)-[(end lunch)-(start lunch)])*24
Then change the format for this cell to "General" or "0.00".