i have a spread sheet i use to work out my overtime and i need to get it to
work out the hours and mins in decimals.

For example

I start at 08:30 so any time before this is overtime
I finish at 17:30 so any time over this is overtime ,
I need it to be displayed as 2.25 instead of 02:15:00

a b C D E
f g
1 start finish overtime
08:30 17:30
2 THU 01/03/2007 06:45:00 18:00 02:15:00

I have it so that when the time is entered it works out the overtime ,f1 and
g1 are hidden so the formular is as follows for this

=IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon

at the moment this is being worked out using 2 cells out of the working area
to produce E2 02:15:00 by adding the two cells together, I have them all
formatted as time so it will display the right data.

can anyone help or am i doomed to doing it long hand forever.

Smoakie.

02:15:00 * 24 format as standard

"Smoakie" skrev:

> i have a spread sheet i use to work out my overtime and i need to get it to
> work out the hours and mins in decimals.
>
> For example
>
> I start at 08:30 so any time before this is overtime
> I finish at 17:30 so any time over this is overtime ,
> I need it to be displayed as 2.25 instead of 02:15:00
>
> a b C D E
> f g
> 1 start finish overtime
> 08:30 17:30
> 2 THU 01/03/2007 06:45:00 18:00 02:15:00
>
> I have it so that when the time is entered it works out the overtime ,f1 and
> g1 are hidden so the formular is as follows for this
>
> =IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon
>
> at the moment this is being worked out using 2 cells out of the working area
> to produce E2 02:15:00 by adding the two cells together, I have them all
> formatted as time so it will display the right data.
>
> can anyone help or am i doomed to doing it long hand forever.
>
> Smoakie.

FX.

=(IF(C2<TIME(8,30,0),TIME(8,30,0)-C2,C2)+IF(D2>TIME(17,30,0),D2-TIME(17,30,0),D2))*24



"excelent" skrev:

> 02:15:00 * 24 format as standard
>
> "Smoakie" skrev:
>
> > i have a spread sheet i use to work out my overtime and i need to get it to
> > work out the hours and mins in decimals.
> >
> > For example
> >
> > I start at 08:30 so any time before this is overtime
> > I finish at 17:30 so any time over this is overtime ,
> > I need it to be displayed as 2.25 instead of 02:15:00
> >
> > a b C D E
> > f g
> > 1 start finish overtime
> > 08:30 17:30
> > 2 THU 01/03/2007 06:45:00 18:00 02:15:00
> >
> > I have it so that when the time is entered it works out the overtime ,f1 and
> > g1 are hidden so the formular is as follows for this
> >
> > =IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon
> >
> > at the moment this is being worked out using 2 cells out of the working area
> > to produce E2 02:15:00 by adding the two cells together, I have them all
> > formatted as time so it will display the right data.
> >
> > can anyone help or am i doomed to doing it long hand forever.
> >
> > Smoakie.

TRY :
=(IF(D2-C2>G1-F1,(D2-C2)-(G1-F1),0))*24


IGNORE FORMULA BELOW

"excelent" skrev:

> FX.
>
> =(IF(C2<TIME(8,30,0),TIME(8,30,0)-C2,C2)+IF(D2>TIME(17,30,0),D2-TIME(17,30,0),D2))*24
>
>
>
> "excelent" skrev:
>
> > 02:15:00 * 24 format as standard
> >
> > "Smoakie" skrev:
> >
> > > i have a spread sheet i use to work out my overtime and i need to get it to
> > > work out the hours and mins in decimals.
> > >
> > > For example
> > >
> > > I start at 08:30 so any time before this is overtime
> > > I finish at 17:30 so any time over this is overtime ,
> > > I need it to be displayed as 2.25 instead of 02:15:00
> > >
> > > a b C D E
> > > f g
> > > 1 start finish overtime
> > > 08:30 17:30
> > > 2 THU 01/03/2007 06:45:00 18:00 02:15:00
> > >
> > > I have it so that when the time is entered it works out the overtime ,f1 and
> > > g1 are hidden so the formular is as follows for this
> > >
> > > =IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon
> > >
> > > at the moment this is being worked out using 2 cells out of the working area
> > > to produce E2 02:15:00 by adding the two cells together, I have them all
> > > formatted as time so it will display the right data.
> > >
> > > can anyone help or am i doomed to doing it long hand forever.
> > >
> > > Smoakie.

unfortunatley when the hours are inside
the alloted time the formular does not work but i take my hat off to you i
have no idea how that works. if d2 is less than 17:30 it displays 00:00 and
so to the c2 cell

any more thoughts gladly recieved

thanks

"excelent" wrote:

> TRY :
> =(IF(D2-C2>G1-F1,(D2-C2)-(G1-F1),0))*24
>
>
> IGNORE FORMULA BELOW
>
> "excelent" skrev:
>
> > FX.
> >
> > =(IF(C2<TIME(8,30,0),TIME(8,30,0)-C2,C2)+IF(D2>TIME(17,30,0),D2-TIME(17,30,0),D2))*24
> >
> >
> >
> > "excelent" skrev:
> >
> > > 02:15:00 * 24 format as standard
> > >
> > > "Smoakie" skrev:
> > >
> > > > i have a spread sheet i use to work out my overtime and i need to get it to
> > > > work out the hours and mins in decimals.
> > > >
> > > > For example
> > > >
> > > > I start at 08:30 so any time before this is overtime
> > > > I finish at 17:30 so any time over this is overtime ,
> > > > I need it to be displayed as 2.25 instead of 02:15:00
> > > >
> > > > a b C D E
> > > > f g
> > > > 1 start finish overtime
> > > > 08:30 17:30
> > > > 2 THU 01/03/2007 06:45:00 18:00 02:15:00
> > > >
> > > > I have it so that when the time is entered it works out the overtime ,f1 and
> > > > g1 are hidden so the formular is as follows for this
> > > >
> > > > =IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon
> > > >
> > > > at the moment this is being worked out using 2 cells out of the working area
> > > > to produce E2 02:15:00 by adding the two cells together, I have them all
> > > > formatted as time so it will display the right data.
> > > >
> > > > can anyone help or am i doomed to doing it long hand forever.
> > > >
> > > > Smoakie.

one other prob i have you may know straight off if i want to enter data into
cells and copy and paste them into a table on another sheet then move it down
automatically ready for the next line of info...all to be done using a macro
, any ideas on that one .....the annoying thing is i did all this in college
about 8 years ago and now i can't remember sqwat..

smoakie

"excelent" wrote:

> TRY :
> =(IF(D2-C2>G1-F1,(D2-C2)-(G1-F1),0))*24
>
>
> IGNORE FORMULA BELOW
>
> "excelent" skrev:
>
> > FX.
> >
> > =(IF(C2<TIME(8,30,0),TIME(8,30,0)-C2,C2)+IF(D2>TIME(17,30,0),D2-TIME(17,30,0),D2))*24
> >
> >
> >
> > "excelent" skrev:
> >
> > > 02:15:00 * 24 format as standard
> > >
> > > "Smoakie" skrev:
> > >
> > > > i have a spread sheet i use to work out my overtime and i need to get it to
> > > > work out the hours and mins in decimals.
> > > >
> > > > For example
> > > >
> > > > I start at 08:30 so any time before this is overtime
> > > > I finish at 17:30 so any time over this is overtime ,
> > > > I need it to be displayed as 2.25 instead of 02:15:00
> > > >
> > > > a b C D E
> > > > f g
> > > > 1 start finish overtime
> > > > 08:30 17:30
> > > > 2 THU 01/03/2007 06:45:00 18:00 02:15:00
> > > >
> > > > I have it so that when the time is entered it works out the overtime ,f1 and
> > > > g1 are hidden so the formular is as follows for this
> > > >
> > > > =IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon
> > > >
> > > > at the moment this is being worked out using 2 cells out of the working area
> > > > to produce E2 02:15:00 by adding the two cells together, I have them all
> > > > formatted as time so it will display the right data.
> > > >
> > > > can anyone help or am i doomed to doing it long hand forever.
> > > >
> > > > Smoakie.

format as standard

=((IF(C2<F1,F1-C2))+IF(D2>G1,D2-G1))*24


"Smoakie" skrev:

> one other prob i have you may know straight off if i want to enter data into
> cells and copy and paste them into a table on another sheet then move it down
> automatically ready for the next line of info...all to be done using a macro
> , any ideas on that one .....the annoying thing is i did all this in college
> about 8 years ago and now i can't remember sqwat..
>
> smoakie
>
> "excelent" wrote:
>
> > TRY :
> > =(IF(D2-C2>G1-F1,(D2-C2)-(G1-F1),0))*24
> >
> >
> > IGNORE FORMULA BELOW
> >
> > "excelent" skrev:
> >
> > > FX.
> > >
> > > =(IF(C2<TIME(8,30,0),TIME(8,30,0)-C2,C2)+IF(D2>TIME(17,30,0),D2-TIME(17,30,0),D2))*24
> > >
> > >
> > >
> > > "excelent" skrev:
> > >
> > > > 02:15:00 * 24 format as standard
> > > >
> > > > "Smoakie" skrev:
> > > >
> > > > > i have a spread sheet i use to work out my overtime and i need to get it to
> > > > > work out the hours and mins in decimals.
> > > > >
> > > > > For example
> > > > >
> > > > > I start at 08:30 so any time before this is overtime
> > > > > I finish at 17:30 so any time over this is overtime ,
> > > > > I need it to be displayed as 2.25 instead of 02:15:00
> > > > >
> > > > > a b C D E
> > > > > f g
> > > > > 1 start finish overtime
> > > > > 08:30 17:30
> > > > > 2 THU 01/03/2007 06:45:00 18:00 02:15:00
> > > > >
> > > > > I have it so that when the time is entered it works out the overtime ,f1 and
> > > > > g1 are hidden so the formular is as follows for this
> > > > >
> > > > > =IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon
> > > > >
> > > > > at the moment this is being worked out using 2 cells out of the working area
> > > > > to produce E2 02:15:00 by adding the two cells together, I have them all
> > > > > formatted as time so it will display the right data.
> > > > >
> > > > > can anyone help or am i doomed to doing it long hand forever.
> > > > >
> > > > > Smoakie.

Im not sure what u mean here but maby

Cells(65500,1).end(xlup).row

find last not empty cell in column A



"Smoakie" skrev:

> one other prob i have you may know straight off if i want to enter data into
> cells and copy and paste them into a table on another sheet then move it down
> automatically ready for the next line of info...all to be done using a macro
> , any ideas on that one .....the annoying thing is i did all this in college
> about 8 years ago and now i can't remember sqwat..
>
> smoakie
>
> "excelent" wrote:
>
> > TRY :
> > =(IF(D2-C2>G1-F1,(D2-C2)-(G1-F1),0))*24
> >
> >
> > IGNORE FORMULA BELOW
> >
> > "excelent" skrev:
> >
> > > FX.
> > >
> > > =(IF(C2<TIME(8,30,0),TIME(8,30,0)-C2,C2)+IF(D2>TIME(17,30,0),D2-TIME(17,30,0),D2))*24
> > >
> > >
> > >
> > > "excelent" skrev:
> > >
> > > > 02:15:00 * 24 format as standard
> > > >
> > > > "Smoakie" skrev:
> > > >
> > > > > i have a spread sheet i use to work out my overtime and i need to get it to
> > > > > work out the hours and mins in decimals.
> > > > >
> > > > > For example
> > > > >
> > > > > I start at 08:30 so any time before this is overtime
> > > > > I finish at 17:30 so any time over this is overtime ,
> > > > > I need it to be displayed as 2.25 instead of 02:15:00
> > > > >
> > > > > a b C D E
> > > > > f g
> > > > > 1 start finish overtime
> > > > > 08:30 17:30
> > > > > 2 THU 01/03/2007 06:45:00 18:00 02:15:00
> > > > >
> > > > > I have it so that when the time is entered it works out the overtime ,f1 and
> > > > > g1 are hidden so the formular is as follows for this
> > > > >
> > > > > =IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon
> > > > >
> > > > > at the moment this is being worked out using 2 cells out of the working area
> > > > > to produce E2 02:15:00 by adding the two cells together, I have them all
> > > > > formatted as time so it will display the right data.
> > > > >
> > > > > can anyone help or am i doomed to doing it long hand forever.
> > > > >
> > > > > Smoakie.

thanks excelent will try and post result
"excelent" wrote:

> Im not sure what u mean here but maby
>
> Cells(65500,1).end(xlup).row
>
> find last not empty cell in column A
>
>
>
> "Smoakie" skrev:
>
> > one other prob i have you may know straight off if i want to enter data into
> > cells and copy and paste them into a table on another sheet then move it down
> > automatically ready for the next line of info...all to be done using a macro
> > , any ideas on that one .....the annoying thing is i did all this in college
> > about 8 years ago and now i can't remember sqwat..
> >
> > smoakie
> >
> > "excelent" wrote:
> >
> > > TRY :
> > > =(IF(D2-C2>G1-F1,(D2-C2)-(G1-F1),0))*24
> > >
> > >
> > > IGNORE FORMULA BELOW
> > >
> > > "excelent" skrev:
> > >
> > > > FX.
> > > >
> > > > =(IF(C2<TIME(8,30,0),TIME(8,30,0)-C2,C2)+IF(D2>TIME(17,30,0),D2-TIME(17,30,0),D2))*24
> > > >
> > > >
> > > >
> > > > "excelent" skrev:
> > > >
> > > > > 02:15:00 * 24 format as standard
> > > > >
> > > > > "Smoakie" skrev:
> > > > >
> > > > > > i have a spread sheet i use to work out my overtime and i need to get it to
> > > > > > work out the hours and mins in decimals.
> > > > > >
> > > > > > For example
> > > > > >
> > > > > > I start at 08:30 so any time before this is overtime
> > > > > > I finish at 17:30 so any time over this is overtime ,
> > > > > > I need it to be displayed as 2.25 instead of 02:15:00
> > > > > >
> > > > > > a b C D E
> > > > > > f g
> > > > > > 1 start finish overtime
> > > > > > 08:30 17:30
> > > > > > 2 THU 01/03/2007 06:45:00 18:00 02:15:00
> > > > > >
> > > > > > I have it so that when the time is entered it works out the overtime ,f1 and
> > > > > > g1 are hidden so the formular is as follows for this
> > > > > >
> > > > > > =IF(c2<f1,f1-c2) for the morning and =IF(D2>G1,D2-G1) for the afternoon
> > > > > >
> > > > > > at the moment this is being worked out using 2 cells out of the working area
> > > > > > to produce E2 02:15:00 by adding the two cells together, I have them all
> > > > > > formatted as time so it will display the right data.
> > > > > >
> > > > > > can anyone help or am i doomed to doing it long hand forever.
> > > > > >
> > > > > > Smoakie.