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(D2G1,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(D2G1,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(D2TIME(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(D2G1,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-C2G1-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(D2TIME(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(D2G1,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-C2G1-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(D2TIME(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(D2G1,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-C2G1-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(D2TIME(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(D2G1,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(D2G1,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-C2G1-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(D2TIME(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(D2G1,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-C2G1-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(D2TIME(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(D2G1,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-C2G1-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(D2TIME(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(D2G1,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.