by Dates are one of the most common items to display to the user, but we need to be careful doing so. Just showing it is quick and easy for us, but can lead to a lot of confusion to the user if the date is not formatted correctly. Let’s use an example to demonstrate this:
06/07/2019 11:45
An American would read June 7th, 2019 but would be confused regarding the hour because there’s no indication of am or pm. A British person would understand the 6th of July and still be confused regarding the hour. Here in Portugal, we would read the 6th of July 2019 at 11:45 and know it’s in the morning because we use the 24h nomenclature.
Dates are hard so simplifying them can be tricky. I have a complete reference where you can explore date formatting and useful examples and formulas that you can use in your projects.
Birthday List Example
The best way to understand something is to do an example, so let’s think about a simple app where I store the name of the person and his/her birthday. I could list the dates, but it’s a lot nicer to look and have all the math done for us.
Formulas Used
For this example, you’ll need to understand the following PowerApps functions:
- If
- Mod
- DateDiff
- Now
- Today
I’ll explain the usage of each further.
SharePoint will store the information
To achieve this, let’s create a SharePoint list with just two fields:
- Title (generated automatically)
- Birthday (Date)
- Birthday Party (Date & Time)
You should have something like this:
Note: Please note that the names are different than the list above. It’s explained further in the article SharePoint: Best Practices. Please take a look to understand why.
Let’s create the PowerApp
Let’s push our PowerApps button to create a new one:
You should see something like this:
Let’s remove the created user since it’s always me and reduce a little the size of the row:
Format the date
Finally, we want to change the data to something more helpful. To do so replace with the following formula:
DateDiff(Today(),birthday,Days) & " day(s) to birthday"
Let’s understand each step of the formula:
- DateDiff will provide us the difference in days between the two dates
& " day(s) to birthday"appends the string to make something like 12 day(s) to birthday.
How about if it’s today? Let’s change the formula to present something better:
If(DateDiff(Today(),birthday,Days) = 0, "Today", DateDiff(Today(),birthday,Days) & " day(s) to birthday")
Let’s understand each step of the formula:
DateDiffwill provide us the difference inDaysbetween both dates- If the
DateDiffis zero means that we should presentToday - If not then show the same as before
Looking better, but how about the birthday party? We have a time associated with it so we should consider it. Let’s add it to the row so that we know how much time we have until the party:
The formula used is almost the same as before, but we replaced it for the correct field “birthdayparty”. But since we have a date and a time it would be nice to see it better formatted. So, let’s add something “2 days 1hour and 15 minutes”.
DateDiff(Today(),birthdayparty,Days) & " days " & Mod(DateDiff(Now(),birthdayparty ,Hours),24) & " hours and " & Mod(DateDiff(Now(),birthdayparty ,Minutes),60) & " minutes "
Looks much more helpful. Notice that for the hours and minutes part of the formula we changed to Now()instead of Today(). Today()only returns the date, so we need to use Now()to get also the time.
Improving just a little bit more
If you want to remove the cases for 0 minutes, for example, you need to include an if. It looks a lot more complicated, but it’s quite simple. Just follow the structure:
if(value = 0,"", value)
It will look something like this:
If(DateDiff(Today(),birthdayparty,Days) = 0, "", DateDiff(Today(),birthdayparty,Days)) & " days " & If(Mod(DateDiff(Now(),birthdayparty ,Hours),24) = 0, "", Mod(DateDiff(Now(),birthdayparty ,Hours),24)) & " hours " & If(Mod(DateDiff(Now(),birthdayparty ,Minutes),60) = 0, "", Mod(DateDiff(Now(),birthdayparty ,Minutes),60)) & " minutes"
You can make further changes to make the string look better, but I think it gets the job done quite nicely.
Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out my other PowerApps articles
Featured Image by Sonja Langford on Unsplash
