Calculated column examples

1)Currentagreed date is greater than now  and status equal to something that time display Yes   and Currentagreed date is less than now  and status equal to something that time display  Invalid 
I have totally four status(red,amber,green,completed).Now means current date
Formula: =IF(OR(AND(CurrentAgreed>Now,Status="Red"),AND(CurrentForecast>Now,Status="Red")),"Yes",
IF(OR(AND(CurrentAgreed>Now,Status="Green"),AND(CurrentForecast>Now,Status="Green")),"Yes",
IF(OR(AND(CurrentAgreed>Now,Status="Amber"),AND(CurrentForecast>Now,Status="Amber")),"Yes",
IF(OR(AND(CurrentAgreed>Now,Status="Completed"),AND(CurrentForecast>Now,Status="Completed")),"Yes",
IF(OR(AND(CurrentAgreed<Now,Status="Red"),AND(CurrentForecast<Now,Status="Red")),"Invalid",
IF(OR(AND(CurrentAgreed<Now,Status="Green"),AND(CurrentForecast<Now,Status="Green")),"Invalid",
IF(OR(AND(CurrentAgreed<Now,Status="Amber"),AND(CurrentForecast<Now,Status="Amber")),"Invalid",
IF(OR(AND(CurrentAgreed<Now,Status="Completed"),AND(CurrentForecast<Now,Status="Completed")),"Invalid"))))))))


2)And combination with multiple conditions

=IF(OR(AND(CurrentAgreed<Now,Status="Red"),AND(CurrentForecast<Now,Status="Red"),AND(CurrentAgreed<Now,Status="Green"),AND(CurrentForecast<Now,Status="Green"),AND(CurrentAgreed<Now,Status="Amber"),AND(CurrentForecast<Now,Status="Amber")),"Invalid","Yes")

3)Using Two if conditions with multiple and conditions
=IF(OR(AND(CurrentAgreed<Now,Status="Red"),AND(CurrentForecast<Now,Status="Red"),
AND(CurrentAgreed<Now,Status="Green"),AND(CurrentForecast<Now,Status="Green"),
AND(CurrentAgreed<Now,Status="Completed"),AND(CurrentForecast<Now,Status="Completed"),
AND(CurrentAgreed<Now,Status="Amber"),AND(CurrentForecast<Now,Status="Amber")),"Invalid",
IF(OR(AND(CurrentAgreed>Now,Status="Red"),AND(CurrentForecast>Now,Status="Red"),
AND(CurrentAgreed>Now,Status="Green"),AND(CurrentForecast>Now,Status="Green"),
AND(CurrentAgreed>Now,Status="Completed"),AND(CurrentForecast>Now,Status="Completed"),
AND(CurrentAgreed>Now,Status="Amber"),AND(CurrentForecast>Now,Status="Amber")),"Yes"))

4)Using div tag inside the if condition

1.       IF  CurrentAgreed>=Today AND CurrentForecast >= Today  THEN  IsValid.Text=”Yes”, IsValid.Backgroundcolor=”none”

2.       No. The IsValid and Status columns are entirely separate.  If CurrentAgreed<Today OR CurrentForecast <today THEN IsValid.Text=”No”, IsValid.Backgroundcolor=”Amber”

 

Formula:=IF(AND(CurrentForecast>Now,CurrentAgreed>Now),"Yes",IF(OR(CurrentForecast<Now,CurrentAgreed<Now),"<DIV style='border: 1px #FFC200 solid;background-color:#FFC200;color:#FFFFFF;'>No<DIV>"))

 

 

 

 

5)Using Multiple if conditions
=IF([Issue Status]="Awaiting SME Action","#4682B4",
IF([Issue Status]="Consultation Provided","#1E90FF",
IF([Issue Status]="Document review /Approval Provided","#2F4F4F",
IF([Issue Status]="Awaiting Seed Funding","#008080",
IF([Issue Status]="Recevied Seed funding","#33CC33",
IF([Issue Status]="No Impacted","#A0522D",
IF([Issue Status]="Closed Request","#FF9999",
IF([Issue Status]="Estimation provided","#008000"))))))))

6)Calculating the difference between two times with hours,minutes and seconds

=TEXT(Opentime-Completedtime,"h:mm:ss")

7)Getting the total time with hours,minutes and seconds

=TEXT(H1time+H2time+H3time+Hcwtime+Hwatime+Hactime,"h:mm:ss")