Formula for time sheet req
 

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

Formula for time sheet req
 

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.

"santaviga" wrote:

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

Formula for time sheet req
 

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

"Joel" wrote:

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.

"santaviga" wrote:

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

Formula for time sheet req
 

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

"santaviga" wrote:

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

"Joel" wrote:

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.

"santaviga" wrote:

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

Formula for time sheet req
 

Hi,

Can you please explain FormX

Mark

"Joel" wrote:

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

"santaviga" wrote:

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

"Joel" wrote:

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.

"santaviga" wrote:

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

Formula for time sheet req
 

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

"santaviga" wrote:

Hi,

Can you please explain FormX

Mark

"Joel" wrote:

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

"santaviga" wrote:

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

"Joel" wrote:

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.

"santaviga" wrote:

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

Formula for time sheet req
 

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

"Joel" wrote:

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

"santaviga" wrote:

Hi,

Can you please explain FormX

Mark

"Joel" wrote:

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

"santaviga" wrote:

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

"Joel" wrote:

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.

"santaviga" wrote:

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

Formula for time sheet req
 

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.

"santaviga" wrote:

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

"Joel" wrote:

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

"santaviga" wrote:

Hi,

Can you please explain FormX

Mark

"Joel" wrote:

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

"santaviga" wrote:

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

"Joel" wrote:

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.

"santaviga" wrote:

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