Automatic date calculation

[catalincirjan]

Hello,

I have a table that I want some people to fill in. The last column is called "Submission date" and I want this to be automatically calculate.

Trigger: When they (start to)complete a row in the table
Rule for calculation:

• If that persons input is before the established cutover date - same day
• If that persons input is after the established cutover date - next business day // I will accept that it will not be able to distinguish holydays and such, but the probabilty of somebody introducing something before a holiday is very unlikely

My attempt of a solution: =IF(A2<>"",IF(HOUR(NOW())<3,TODAY(),IF(WEEKDAY(TODAY(),2)=5,TODAY()+3,TODAY()+1)),"")
Reasoning:
Now()<3 -> is the cutover date with correction for timezone when I uplod this to Sharepoint ( although I am UTC+3, once on sharepoint I guess it takes the timezon of the server - America?!)
Today()+3 -> in case it's a Friday the result will be Monday

Other details: Before I upload this file, I want to protect that specific column so that people can't 'overwrite' / 'forge' the submission date
Issue: I used Power Automate to send an automaticaly report each day.
it appears that anytime I open the excel it (or the power flow runs), the formula recalculates the submission date. So if person X would have writen something last week, when I open now the excel it will show

I have tried to go to Formula -> Calculation Options -> Manual // this almost works -> when I edit the file after cutover hour, the old entries remain with the old date ... until I save .... which changes all the dates again
Also, the Manual Calculation is not an option due to the fact specified in Other details: once protected I can no click in the formula of that cell and press enter

Any suggestions, please?

 

[mole999]

you probably really need to use VBA to set the value once only, as you have found =Now() just changes

 

[catalincirjan]

Hmmm the issue with VBA is that I have zero experience, I am not even sure if I can upload it to Sharepoint because I think they don't accept macros in the online version ...