DIFFDAY with a defined date and TODAY()

[FelipeCaru]

Hello everyone,

In my Sharepoint list I have 3 dates: "Start date" and "end date" are both introduced by the user when creating a new registry; "real end date" can be added later when the user finishes a proyect, so it is in the edit view. From those 3, I have a calculated camp in Sharepoint that is the DIFDAY between "start date" and "end date", which is a static number of "how many days I have left".

However, what I want to do is a DIFDAY between TODAY() and the "end date", number which will constantly change. Is it posible to do this? For example, if "end day" is 20th and TODAY() is 18; the difday would give 2; tomorrow would be 1; on the 20th, zero; and on the 21, -1.

If it is possible, how can it be done so?

[RandyHayes · Accepted solution]

@FelipeCaruyou say "in my SharePoint".  Does this mean you are looking for an answer for a SharePoint column, or are you looking for this in PowerApps?

If in PowerApps, then yes - the DateDiff is the function you will use.

Assume End Date is 2/20/2019 : DateDiff(Today(), EndDate) = 2
Assume End Date is 2/17/2019 : DateDiff(Today(), EndDate) = -1

Hope this helps some.

[v-xida-msft · Accepted solution]

Hi @FelipeCaru ,

Could you please share a bit more about your scenario?

Do you want to calculate the difference between TODAY() and the "end date" within PowerApps or your SP list?

If you want to calculate the difference between TODAY() and the "end date" within PowerApps, I think the DateDiff() function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

Add a Gallery control within your app, set the Items property to following:

'YourSPList'

Add a Label control within your Gallery, set the Text property to following:

DateDiff(Today(), ThisItem.'end date', Days)  /* <-- the 'end date' represents the end date column in your SP list */

More details about DateDiff function in PowerApps, please check the following article:

DateDiff function

If you want to achieve your needs in your SP list, I afraid that there is no direct way to achieve your needs. Currently, within SP list, we could not use TODAY() function directly within calculated column. Please check the following article for more details:

https://sharepoint.stackexchange.com/questions/151144/how-to-use-today-and-me-in-calculated-column

Best regards,

[FelipeCaru]

Thanks for the answers.

A question I am unsure of. I have as working code what was said by both of you (again, thanks), however, am unsure if that code will only work for today's date, or will it continue updating, meaning, tomorrow I will have same number or "minus 1" because it is the next day.

If(DateDiff(Today();'Fecha Termino')<0;"Atrasado";DateDiff(Today();'Fecha Termino'))

For example, in above code, if the diffdate is <0, it will be "late" ('atrasado'), and else, will give the value. If the "end date" is the 20th, and today is 19; the number given will be, as in today feb19, "1". Will this number keep being "1" tomorrow, or go to zero as it should due to "today"? Meaning, is this TODAY() "static or dinamic", so to speak?

Thanks in advance,

[RandyHayes · Accepted solution]

Today() is always dynamic and is the date of execution.

Today() is 2/19/2019

Running Today() tomorrow will be 2/20/2019

[FelipeCaru]

Thank you ever so much!

Have left this as solved now since don't have any more questions 🙂

[pradeep2091]

I need the date difference like if we enter yesterday's date,it should show 1 day ago