Formula for time sheet req

[Guest]

Hi folks,

I have a time sheet with the following on it.

Start Finish Total Overtime
Time Back
0700 1600 8:00hrs 0:30mins
0:00

what I need is a formula so when a time is entered as 0:00 through start and
finish this registers as 7:30 in the time back column, also I need it to
return 0:00 in overtime column but it is returing ############# as it is
negative.

Dies anyone have any ideas for me, would be much appreciated.

Regards,

Mark

 

[Guest]

what you asked for is not clear. I think you need a "Time Out" and a "time
back" column. It look like there is 9hrs of work. And a 1 hour lunch. Can
you send the formulas you are using for each column so we can get all the
details. Is lunch alutomatically set to 12:00 so time back is the time from
12:00


You probaly need an if statement in your formula to handle negative numbers.

 

[Guest]

Hi,

This is what I have.

Start Lunch Finish Overtime
08:30 12:00 13:00 17:00 00:00
08:30 12:00 13:00 17:30 00:30
08:30 12:00 13:00 16:30 -00:30

I can get the formula so this returns any overtime I have over 7.50 hours
per day and so on if I work over 7.50 hours. What I need is a formula to
calculate and subtract if I work under 7.50 hours per day and subtract this
from an overtime total, also if an entry is all 00:00 across the cells for it
to recognise as 7.50 hours taken as Time Off.

Hope you understand this.

Mark

 

[Guest]

you presently have for overtime a formula = FormX
use this if statements

=if(FormX <= 0,0,FormX)
if statement has following format
=if( equation, If true, if false)
You want to put 0 if True (less than = 0), otherwise, use your formula FormX

 

[Guest]

Hi,

Can you please explain FormX

Mark

you presently have for overtime a formula = FormX
use this if statements

=if(FormX <= 0,0,FormX)
if statement has following format
=if( equation, If true, if false)
You want to put 0 if True (less than = 0), otherwise, use your formula FormX

 

[Guest]

It is your formula that you are presently using in the overtime column.

 

[Guest]

Sorry Joel, still not getting what I need, Is there a way to calculate say
07:00 - 08:00 to return -01:00 in time format this might help me.

Mark

 

[Guest]

Send me your formula for overtime and I will send you the correctt code.

subtracting 07:00 - 08:00 will never give you a time answer. The answer
would be Dec 31, 1899 at 11:00PM. Excel uses Jan 1, 1900 as time 0. You
need to convert results of the subtraction to a number format which will give
you -.041666666 which is really -1/24hrs because microsoft time is fractional
number with 1 equalling days.