working with time functions
 

i am making a spreadsheet of hours people have worked in a week. People
should work 37 hours, anything above that is credit, and below it debit. In
my spreadsheet, the "hours worked" cell is displayed in 37:00 format (as is
all other times in the worksheet) if someone works 37:30, that 30 minutes is
displayed in the credit/debit cell fine, but if someone works less than 37:00
in the week nothing i do works to show the debit people are accrueing. So if
someone works 35:00 i would like it to show that they are in 02:00 debit. Has
anyone got any suggestions?

Chris,

I guess that the problem you get for a it is seeing ####.

This is not a problem apart from presentation, there is still a negative
time in there with you can do arithmetic on.

One way would be to work in decimal hours, =(B1-A1)*24, and format as
general.

A better way would be to have a credit and debit cell separately, and then
sum those and take individually and subtract debit from credit.
=(B1-A10)*(B1-A1)
=(A1-B10)*(A1-B1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris01623" wrote in message
...
i am making a spreadsheet of hours people have worked in a week. People
should work 37 hours, anything above that is credit, and below it debit.
In
my spreadsheet, the "hours worked" cell is displayed in 37:00 format (as
is
all other times in the worksheet) if someone works 37:30, that 30 minutes
is
displayed in the credit/debit cell fine, but if someone works less than
37:00
in the week nothing i do works to show the debit people are accrueing. So
if
someone works 35:00 i would like it to show that they are in 02:00 debit.
Has
anyone got any suggestions?

In Excel, time is a formated decimal fraction of 24 hour days so credit
would be
=IF(worked37/24,worked-37/24,"")
and debit would be
=IF(worked<37/24,37/24-worked,"")

Jerry

Chris01623 wrote:

i am making a spreadsheet of hours people have worked in a week. People
should work 37 hours, anything above that is credit, and below it debit. In
my spreadsheet, the "hours worked" cell is displayed in 37:00 format (as is
all other times in the worksheet) if someone works 37:30, that 30 minutes is
displayed in the credit/debit cell fine, but if someone works less than 37:00
in the week nothing i do works to show the debit people are accrueing. So if
someone works 35:00 i would like it to show that they are in 02:00 debit. Has
anyone got any suggestions?

"?B?Q2hyaXMwMTYyMw==?="
wrote in :

i am making a spreadsheet of hours people have worked in a week.
People should work 37 hours, anything above that is credit, and below
it debit. In my spreadsheet, the "hours worked" cell is displayed in
37:00 format (as is all other times in the worksheet) if someone works
37:30, that 30 minutes is displayed in the credit/debit cell fine, but
if someone works less than 37:00 in the week nothing i do works to
show the debit people are accrueing. So if someone works 35:00 i would
like it to show that they are in 02:00 debit. Has anyone got any
suggestions?

You can change a setting in Excel to show negative time:

http://support.microsoft.com/default...b;en-us;182247

Doing so has a drawback but I cannot remember what it is.


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)