cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FelipeCaru
Advocate V
Advocate V

DIFFDAY with a defined date and TODAY()

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?

3 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Today() is 2/19/2019

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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,

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

Today() is 2/19/2019

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Thank you ever so much!

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,884)