IF Formula Add a Date if complete

%3CLINGO-SUB%20id%3D%22lingo-sub-1430466%22%20slang%3D%22en-US%22%3EIF%20Formula%20Add%20a%20Date%20if%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430466%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20sharepoint%20this%20and%20I%20need%20to%20add%20the%20date%20and%20time%20when%20the%20status%20is%20changed%20to%20complete.%20At%20the%20moment%20the%20person%20who%20is%20editing%20the%20status%20is%20manually%20adding%20the%20date%20and%20i%20want%20this%20to%20just%20add%20it%20automatically%20I%20have%20tried%20all%20sorts%20of%20formulas%20and%20nothing%20seems%20to%20work%20(I%20am%20not%20hte%20best%20at%20IF%20formulas)%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20I%20have%3A%3C%2FP%3E%3CP%3E%3DIF(%5BCompletion%20Date%20-%20Jul%5D%3D%22Complete%22%2CNOW())%3C%2FP%3E%3CP%3EThis%20just%20adds%20No%20to%20all%20columns.%20I%20need%20to%20use%20NOW%20as%20I%20need%20date%20and%20time%2C%20I%20am%20sure%20this%20should%20be%20easy%20but%20I%20dont%20seem%20to%20get%20it%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1430466%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430706%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20Add%20a%20Date%20if%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430706%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F685233%22%20target%3D%22_blank%22%3E%40LornaB%3C%2FA%3E%26nbsp%3Byour%20formula%20won't%20work%20because%20you%20haven't%20told%20it%20what%20to%20do%20if%20it%20%3CU%3Edoesn't%3C%2FU%3E%20equal%20Completed.%20Try%20this%3A%20%3CSTRONG%3E%3DIF(%5BCompletion%20Date%20-%20Jul%5D%3D%22Completed%22%2CNOW()%2C%22%22)%3C%2FSTRONG%3E%20which%20will%20leave%20the%20column%20empty%20if%20the%20Completion%20Date%20-%20Jul%20column%20is%20anything%20other%20than%20%22Completed%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20case%20I%20have%20to%20add%208%20hours%20because%20the%20Now()%20function%20always%20returns%20the%20date%2Ftime%20in%20UTC%20whatever%20the%20regional%20settings%20are%20set%20to.%20So%20my%20formula%20is%20%3DIF(%5BCompletion%20Date%20-%20Jul%5D%3D%22Completed%22%2CNOW()%2B(8%2F24)%2C%22%22)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22NOW-function.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195693i3F5F58081FAA8E93%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22NOW-function.png%22%20alt%3D%22NOW-function.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1433861%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20Add%20a%20Date%20if%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1433861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20this%2C%20I%20have%20managed%20to%20get%20it%20to%20work%20as%20I%20was%20also%20meant%20to%20point%20it%20at%20the%20Status%20column%20I%20realised%20as%20that%20is%20where%20it%20states%20complete%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%5BStatus%20-%20Jul%5D%3D%22Complete%22%2CNOW()%2B(8%2F24))%3C%2FP%3E%3CP%3Ethank%20you%20for%20the%20tip%20regarding%20the%20time%20that%20was%20super%20helpful%20as%20I%20would%20never%20have%20got%20that%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

2 Replies

@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),"") 

 

NOW-function.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Highlighted

@Rob Elliott 

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