Condition on Time
 

I calculate the number of hours daily worked. I have column A: timeIn, Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this: If
the number of hours in column C is greater than 4:00 hours, then 30 minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.

Condition on Time
 

in C3 put:

=(B3-C3)*24 to convert time to decimal hours e.g. 7:30 ==7.5 and format
cell as general

in D3

=If(C34,C3-0.5,C3)

HTH


"Chris" wrote:

I calculate the number of hours daily worked. I have column A: timeIn, Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this: If
the number of hours in column C is greater than 4:00 hours, then 30 minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.

Condition on Time
 

Thanks HTH,
It works, but I still need to see the result as hours: hh:mm, at the moment
I end up with only a digit, with lots of decimals, can the result in D3 be
converted back into time? I have tried to format the cell to Time, but it
does not work.
Thanks for your help.

Chris.

"Toppers" wrote:

in C3 put:

=(B3-C3)*24 to convert time to decimal hours e.g. 7:30 ==7.5 and format
cell as general

in D3

=If(C34,C3-0.5,C3)

HTH


"Chris" wrote:

I calculate the number of hours daily worked. I have column A: timeIn, Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this: If
the number of hours in column C is greater than 4:00 hours, then 30 minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.

Condition on Time
 

=IF(C34,(C3-0.5)/24,C3/24)

format as hh:mm

"Chris" wrote:

Thanks HTH,
It works, but I still need to see the result as hours: hh:mm, at the moment
I end up with only a digit, with lots of decimals, can the result in D3 be
converted back into time? I have tried to format the cell to Time, but it
does not work.
Thanks for your help.

Chris.

"Toppers" wrote:

in C3 put:

=(B3-C3)*24 to convert time to decimal hours e.g. 7:30 ==7.5 and format
cell as general

in D3

=If(C34,C3-0.5,C3)

HTH


"Chris" wrote:

I calculate the number of hours daily worked. I have column A: timeIn, Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this: If
the number of hours in column C is greater than 4:00 hours, then 30 minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.

Condition on Time
 

Hi HTH,
It worked, fantastic, Thank you!
Chris

"Toppers" wrote:

=IF(C34,(C3-0.5)/24,C3/24)

format as hh:mm

"Chris" wrote:

Thanks HTH,
It works, but I still need to see the result as hours: hh:mm, at the moment
I end up with only a digit, with lots of decimals, can the result in D3 be
converted back into time? I have tried to format the cell to Time, but it
does not work.
Thanks for your help.

Chris.

"Toppers" wrote:

in C3 put:

=(B3-C3)*24 to convert time to decimal hours e.g. 7:30 ==7.5 and format
cell as general

in D3

=If(C34,C3-0.5,C3)

HTH


"Chris" wrote:

I calculate the number of hours daily worked. I have column A: timeIn, Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this: If
the number of hours in column C is greater than 4:00 hours, then 30 minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.

Condition on Time
 

=IF(C3TIME(4,0,0),C3-TIME(0,30,0),C3)
or
=IF(C31/6,C3-1/48,C3)
--
David Biddulph

"Chris" wrote in message
...
I calculate the number of hours daily worked. I have column A: timeIn,
Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this:
If
the number of hours in column C is greater than 4:00 hours, then 30
minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.

Condition on Time
 

See David's reply too: might (is!) better.
"Chris" wrote:

Hi HTH,
It worked, fantastic, Thank you!
Chris

"Toppers" wrote:

=IF(C34,(C3-0.5)/24,C3/24)

format as hh:mm

"Chris" wrote:

Thanks HTH,
It works, but I still need to see the result as hours: hh:mm, at the moment
I end up with only a digit, with lots of decimals, can the result in D3 be
converted back into time? I have tried to format the cell to Time, but it
does not work.
Thanks for your help.

Chris.

"Toppers" wrote:

in C3 put:

=(B3-C3)*24 to convert time to decimal hours e.g. 7:30 ==7.5 and format
cell as general

in D3

=If(C34,C3-0.5,C3)

HTH


"Chris" wrote:

I calculate the number of hours daily worked. I have column A: timeIn, Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this: If
the number of hours in column C is greater than 4:00 hours, then 30 minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.

Condition on Time
 

Another way....

=IF(C3--"4:00",C3-"0:30",C3)


"Chris" wrote:

I calculate the number of hours daily worked. I have column A: timeIn, Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this: If
the number of hours in column C is greater than 4:00 hours, then 30 minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.

Condition on Time
 

Thank you all, it is now working very nicely, It is great!
Chris.

"Chris" wrote:

I calculate the number of hours daily worked. I have column A: timeIn, Column
B: TimeOut, Column C calculates the total hours done, which is fine. I am
trying to enter a formula in column D which will say something like this: If
the number of hours in column C is greater than 4:00 hours, then 30 minutes
have to be substracted to it.
How can I do this?
I have tried : IF(C3Time(4,0,1), "-0:30"), to no avail.
Any help appreciated.