locked
Condition formula in Calculation based on other columns RRS feed

  • 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

    Please someone can help..

    Thanking you in advance!

     



    • Moved by Mike Walsh FIN 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

Answers

  • 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