1

I've got a SharePoint list that is used to work out renewal dates of software. The three key columns are "NCE SKU", "NCE Purchase Date" and "NCE Renewal Date". The "NCE Renewal Date" column is calculated. It has an IF statement that looks up the value of "NCE SKU" and uses that to determine how many months to add to the value in "NCE Purchase Date" which gives me the renewal date. The formula I am using is:

=IF([NCE SKU]="NCE1",DATE(YEAR([NCE Purchase Date]),MONTH([NCE Purchase Date])+1,DAY([NCE Purchase Date])),
IF([NCE SKU]="NCE12",DATE(YEAR([NCE Purchase Date]),MONTH([NCE Purchase Date])+12,DAY([NCE Purchase Date])),"n/a"))

As you can see if the value of "NCE SKU" is "NCE1" it adds a month to the date value it gets from "NCE Purchase Date" and if the "NCE SKU" is "NCE12" it adds 12 months.

In the SharePoint list this looks to be working correctly:

enter image description here

This is where the weirdness starts, I am running a Flow against this, a simple Flow that sends an email if todays date matches the date shown in the "NCE Renewal Date" column. If I look at the output from Flow I can see that the renewal data is 1 day out:

enter image description here

This of course means I can't match todays date against it and the Flow fails. I can work around this issue by tweaking the Flow but there has to be a reason to why SharePoint is showing one date in the List and a different date in the Flow output?

If anyone can shed some light on this or suggest a way I can tweak the calculated column formula, I was thinking maybe if I could strip out the time from the field that would help?

Thanks Rob

Update:

As identified by theChrisKent the issue was time zones in Flow vs SharePoint. To get around the issue I've now wrapped the DATE functions in the calculated column with TEXT which allows me to format the date and remove the time portion. Now when the flow runs its just doing a text match which works perfectly.

=IF([NCE SKU]="NCE1",TEXT(DATE(YEAR([NCE Purchase Date]),MONTH([NCE Purchase Date])+1,DAY([NCE Purchase Date])),"dd/MM/yyyy"),IF([NCE SKU]="NCE12",TEXT(DATE(YEAR([NCE Purchase Date]),MONTH([NCE Purchase Date])+12,DAY([NCE Purchase Date])),"dd/MM/yyyy"),"n/a"))

Thanks

2
  • It's definitely a timezone issue. The SharePoint List is formatting the date according to your site's regional settings. I'd bet the value in Flow is the UTC value for the date. I'm writing this as a comment since I can't validate the solution but you should be able to use the utcNow and/or convertFromUTC functions in Power Automate to get your comparisons right. Commented May 4, 2022 at 20:49
  • Ah, timezones! yes that makes sense. I had a look at doing the fix in Flow but in the end I cheated and wrapped the calculated column DATE functions in TEXT functions, this removed the time stamp from the field and meant that everything matches.
    – RobC_CTL
    Commented May 6, 2022 at 9:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.