Timesheet Formula: I am trying to create a formula to calculate whether the
hours used are either time and a half between x-y hours. Completely useless
on Excel.

Depends upon the rules.

For instance, if anything over 8 hours is overtime then

=MAX(end_time-start_time-TIME(8,0,0),0)

If it is any hours outside ofv pre-set start and end times, say 08:00 and
16:00 then

=MAX(TIME(8,0,0)-start_time,0)+MAX(end_time-TIME(16,0,0),0)

--

HTH

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


"AHurd" wrote in message
...
Timesheet Formula: I am trying to create a formula to calculate whether
the
hours used are either time and a half between x-y hours. Completely
useless
on Excel.

Sounds good but where do I put the overtime rate?

"Bob Phillips" wrote:

Depends upon the rules.

For instance, if anything over 8 hours is overtime then

=MAX(end_time-start_time-TIME(8,0,0),0)

If it is any hours outside ofv pre-set start and end times, say 08:00 and
16:00 then

=MAX(TIME(8,0,0)-start_time,0)+MAX(end_time-TIME(16,0,0),0)

--

HTH

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


"AHurd" wrote in message
...
Timesheet Formula: I am trying to create a formula to calculate whether
the
hours used are either time and a half between x-y hours. Completely
useless
on Excel.

Just multiply that by the rate

--

HTH

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


"AHurd" wrote in message
...
Sounds good but where do I put the overtime rate?

"Bob Phillips" wrote:

Depends upon the rules.

For instance, if anything over 8 hours is overtime then

=MAX(end_time-start_time-TIME(8,0,0),0)

If it is any hours outside ofv pre-set start and end times, say 08:00
and
16:00 then

=MAX(TIME(8,0,0)-start_time,0)+MAX(end_time-TIME(16,0,0),0)

--

HTH

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


"AHurd" wrote in message
...
Timesheet Formula: I am trying to create a formula to calculate
whether
the
hours used are either time and a half between x-y hours. Completely
useless
on Excel.

Give an example (complete with input and output) of what you are trying to
acieve.

Mangesh


"AHurd" wrote in message
...
Timesheet Formula: I am trying to create a formula to calculate whether
the
hours used are either time and a half between x-y hours. Completely
useless
on Excel.

From To Rate
Mon- Fri 8.00 18.00 S/T
18.00 20.00 T/Half
20.00 6.00 D/T
6.00 8.00 T/Half

Saturday 8.00 13.00 T/Half
13.00 8.00 D/T

Sunday 8.00 8.00 D/T

Therefore when I complete a timesheet and someone who has basic hours of
07:00 - 16:00 but works overtime (mon-fri) to 20:00 I need to input a formula
to calculate the difference between single time and time/half.

"Mangesh Yadav" wrote:

Give an example (complete with input and output) of what you are trying to
acieve.

Mangesh


"AHurd" wrote in message
...
Timesheet Formula: I am trying to create a formula to calculate whether
the
hours used are either time and a half between x-y hours. Completely
useless
on Excel.

Mangesh,

Did you come up with a forumula? I tried to do the one Bob gave me but it
kept coming up with #?NAME error.

"AHurd" wrote:

From To Rate
Mon- Fri 8.00 18.00 S/T
18.00 20.00 T/Half
20.00 6.00 D/T
6.00 8.00 T/Half

Saturday 8.00 13.00 T/Half
13.00 8.00 D/T

Sunday 8.00 8.00 D/T

Therefore when I complete a timesheet and someone who has basic hours of
07:00 - 16:00 but works overtime (mon-fri) to 20:00 I need to input a formula
to calculate the difference between single time and time/half.

"Mangesh Yadav" wrote:

Give an example (complete with input and output) of what you are trying to
acieve.

Mangesh


"AHurd" wrote in message
...
Timesheet Formula: I am trying to create a formula to calculate whether
the
hours used are either time and a half between x-y hours. Completely
useless
on Excel.

That is because you either need to name the cells with the start and end
times as start_time and end_time, or substitute those cells into the
formula.

--

HTH

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


"AHurd" wrote in message
...
Mangesh,

Did you come up with a forumula? I tried to do the one Bob gave me but it
kept coming up with #?NAME error.

"AHurd" wrote:

From To Rate
Mon- Fri 8.00 18.00 S/T
18.00 20.00 T/Half
20.00 6.00 D/T
6.00 8.00 T/Half

Saturday 8.00 13.00 T/Half
13.00 8.00 D/T

Sunday 8.00 8.00 D/T

Therefore when I complete a timesheet and someone who has basic hours of
07:00 - 16:00 but works overtime (mon-fri) to 20:00 I need to input a
formula
to calculate the difference between single time and time/half.

"Mangesh Yadav" wrote:

Give an example (complete with input and output) of what you are
trying to
acieve.

Mangesh


"AHurd" wrote in message
...
Timesheet Formula: I am trying to create a formula to calculate
whether
the
hours used are either time and a half between x-y hours. Completely
useless
on Excel.