Forum Discussion

LornaB's avatar
LornaB
Copper Contributor
Jun 01, 2020

IF Formula Add a Date if complete

I have a sharepoint this and I need to add the date and time when the status is changed to complete. At the moment the person who is editing the status is manually adding the date and i want this to just add it automatically I have tried all sorts of formulas and nothing seems to work (I am not hte best at IF formulas) 

Currently I have:

=IF([Status - Jul]="Complete",NOW())

This just adds No to all columns and numbers to the complete columns. I need to use NOW as I need date and time, I am sure this should be easy but I dont seem to get it right.

 

Any help would be greatly appreciated

  • RobElliott's avatar
    RobElliott
    Silver Contributor

    LornaB your formula won't work because you haven't told it what to do if it doesn't equal Completed. Try this: =IF([Completion Date - Jul]="Completed",NOW(),"") which will leave the column empty if the Completion Date - Jul column is anything other than "Completed"

     

    In my case I have to add 8 hours because the Now() function always returns the date/time in UTC whatever the regional settings are set to. So my formula is =IF([Completion Date - Jul]="Completed",NOW()+(8/24),"") 

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User

    • store's avatar
      store
      Copper Contributor

      re RobElliott 

      I found this thread and am attempting to do something similar. However, wouldn't the NOW() function return the current date meaning it will change depending on the date you run it? I'm hoping to return a static date of when the item was marked "Complete"

      Can you share how I might do that?

      In the below example I have used your above method but expecting this date to update tomorrow.

       

       

    • LornaB's avatar
      LornaB
      Copper Contributor

      RobElliott 

      Thank you very much for this, I have managed to get it to work as I was also meant to point it at the Status column I realised as that is where it states complete 

      =IF([Status - Jul]="Complete",NOW()+(8/24))

      thank you for the tip regarding the time that was super helpful as I would never have got that 

Resources