# Condition formula in Calculation based on other columns • ### Question

• Hi everyone,

I'm running sharepoint 3.0 on a Windows Server 2003. I have a simple Time Attendance List with 3  main columns, Time In, Time Out, Total Working Hours and Overtime.

I'm using the following formula to calculate Total Working Hours  =TEXT([Time Out]-[Time In],"HH:MM") which is working fine.

My problem is that I'm unable to get the column Overtime right. I must calculate Overtime if the Total Working Hours is greater than 9 hours. I use the command IF but it is not working. Now If the THW is greater than 9 it is ok. The calculation runs fine, just simple substraction. But if the THW is less than 9 hours it then returns the number of hours missing to reach 9 hours of work  =TEXT([Total Working Hours]-"09:00","HH:mm")   or a negative figures if  I use this formula =if([Total Working Hours]>9, [Total Working Hours-9],0)

Overtime must calculated only if the TWH is greater than 9 hours otherwise it should display 0

• Moved by Friday, April 15, 2011 2:57 PM admin q not general (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
Friday, April 15, 2011 2:35 PM

• Hello..

I finally made it through.....ouff... working formula

=IF([Total Working Hours]>"09:00",TEXT([Total Working Hours]-"09:00","HH:mm"),"00:00")

With all columns as  Date and Time

Cheers!

Friday, April 15, 2011 6:53 PM

### All replies

• Hi Jean,

I just tried this.

• Create column of type number for Time IN and Time Out.
• Create column of type calculated type  for Total Working hours [TWH](formula Time out - Time In]
• Create column of type calculated type for Over time (formula :- =IF(TWH>9,TWH-9,0) )

If you are creating the columns as dates,rather than numbers, try getting the numbers from the dates, it will make things easier.

This worked in my case and I can see the results.

Hope this helps.

Thanks

V

V
Friday, April 15, 2011 3:10 PM
• Thank you, the IF work fine if the columns are number type as you mentioned. But the problem is the hours issue after 60mins.

I need to keep the column as Date and Time

Thanks,

JN

Friday, April 15, 2011 5:10 PM
• Hello..

I finally made it through.....ouff... working formula

=IF([Total Working Hours]>"09:00",TEXT([Total Working Hours]-"09:00","HH:mm"),"00:00")

With all columns as  Date and Time

Cheers!

Friday, April 15, 2011 6:53 PM