need to create a formula to create a timesheet but haven't a clue

[Guest]

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.

 

[Bob Phillips]

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)

 

[Mangesh Yadav]

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

Mangesh

 

[Guest]

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.

 

[Guest]

Sounds good but where do I put the overtime rate?

 

[Bob Phillips]

Just multiply that by the rate

--

HTH

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

 

[Guest]

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.

 

[Bob Phillips]

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)