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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >