how to manipulate date in sharepoint list item

[wija]

Hello,

i have column name:

- Expiration Date

- Reminder Days

i want to get the Reminder Date, for example:

expiration date: january 23, 2020    minus    reminder days: 31   =   december 23, 2019

i want the december 23, 2019 to be send by email so i can check the reminder date value.

[leyburn19]

Use Filter like:

Expiration_x0020_Date eq 'addDays(utcNow(),-31)'

This will get you the items that equal that date.  Notes:

1. I use the _x0020_ as you column name shows a space
2. You may need to modify the utcNow to be based on your time zone EG I am in a zone =10 hours so I use addDays(addHours(utcNow(),10),-31)
3. The expression is built using the expression builder and must be between ''

I note your reminder days are in a separate column so might be variable  so you might need to tweak to get the result you want with two get items to get the data you want

[yashag2255]

Hi @wija / @leyburn19 

For getting the correct time zone, it is advised to either convert time in Flow or change the regional settings of the share point site. 

Scenarios here: 

1. If the flow is sending time converted to your time zone to SharePoint, change the list settings to point UTC time zone. 

2. If the flow is sending UTC time, then change the regional settings in SharePoint to your timezone.

I have listed this here: https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/10-MS-Flow-hacks-troubleshoots-wor... 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[wija]

Thank you for your response,

what is utcNow() ? to get current date or to get my timezone?

and i dont want to be fixed -31.

i want flexible based on "Reminder Days" column (a.k.a  - Reminder Days).

how we can achieve this?

[yashag2255]

Hey @wija 

The utcnow() expression gets you the current date and time in the UTC timezone as yyyy-MM-ddThh:mm:ssz. 

If you want to use the remainder days then you can get that from the dynamic selector and use it in place of -31. Note that if this is a single line of text in SharePoint, you might have to use the int() function to convert it to an integer first. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[wija]

oh, then seems like i can't use utcNow(),

instead of using utcNow()...., i want to use the value from my "Expiration Date" column as my current date then minus value of "Reminder Days".

row by row

can we achieve this?

[wija]

something like this, but this expression still not correct

formatDateTime(addDays(item()?['Expiration_x0020_Date'], -['Reminder_x0020_Days']), 'dd-MMM-yyyy')

[yashag2255]

Hey @wija 

Can you try this:

formatDateTime(addDays(item()?['Expiration_x0020_Date'], -item()?['Reminder_x0020_Days']), 'dd-MMM-yyyy')

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[leyburn19]

If the post by yashag2255 does not work then you need to do two get items on your share point list as follows:

1.  get Items with no filter
2.  Create a compose that extracts the Reminder Days.  Use the mul() expression to turn it to a negative. eg: mul(remiderdate column,-1) .  This should put the compose into an apply to each
3.  Do Get Items again with a filter like addDays(item()?['Expiration_x0020_Date'],compose)  This should create another apply to each that gets your result