cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Boneckrh19
Level 8

Formula Syntax; If(), Text(), and DateAdd specifically

I'm new to powerapps and I'm having a bit of trouble understanding how the formulas work. I've not been finding anything to explain the syntax for multiple operations in the DateAdd function, though I *think* I found that in the If function, multiple operations are possible by separating with a semicolon. Trying to apply this information is getting very messy, since I so far haven't seen a way to create a new variable or refer to a variable that has been previously changed. Perhaps this would be better explained with an example.

 

 

If( 
Not(IsBlank(ThisItem.HolidayDate)),

Text(DateAdd(ThisItem.HolidayDate, Today() - 1900, Years), "[$-en-US]dddd, mmmm dd, yyyy"),

Text(DateAdd(DateAdd(Today(), HolidayMonth - Today(), Months), HolidayNumOfWeeksIn*7 + HolidayDayOfWeek - 1 - Today(), Days),
"[$-en-US]dddd, mmmm dd, yyyy")
)

This looks horribly convoluted to me, but there doesn't appear to be a good way to break these functions into smaller bits because there's no way to make or refer to a variable after it's been changed except to keep stacking the changes. This code produces a completely blank field. If I take away the "else" portion, it's still blank.

 

 

The breakdown of this would go as follows:

 

 

If the HolidayDate field is not blank (ie, a holiday with a known date like Christmas),

Find today's year and add it to HolidayDate, and subtract the year that HolidayDate comes with. 
(Basically, I'm trying to replace the year from the input with the current year.) Display this final date with the weekday, month, day, and year.
(No abbreviations for weekday and month)

Else, (There is no date ie, Thanksgiving)
Find todays date, subtract todays month and add HolidayMonth
(To replace todays month with the given month)
add HolidayNumOfWeeks*7
(number of days to add based on how many weeks into the given month that holiday is)
add HolidayDayOfWeek - 1
(number of days into the week it is, ie for Thanksgiving it's a 5 for Thursday)
subtract Todays date
(to get rid of the current number of days in we are)
Display this final date with the weekday, month, day, and year.
(No abbreviations for weekday and month)

Basically, I want to display the current years date for any holiday given, even the ones like Thanksgiving which have to be found with information like "the fourth Thursday in November."

 

I've got a CDS list with this information:

CDS HolidayList.png

 

(Hilariously, the CDL displays these dates as one day off from what they were imported as from Excel. This causes the new years date to not show up at all, since it's trying to display day "0" which does not exist. Yet in the powerapp the date is displayed correctly. Go figure.)

But as you can see, Thanksgiving is shown as being in month 11, and day 5 of week 4. (November, thursday of week 4.) Hopefully it makes sense to take that NumberOfWeeks variable and times it by 7 to get how many days in based on the weeks, and then add the number of days listed in the DayOfWeek variable to get how many more days in based on the weekday. 

 

I'm just trying to make this application future proof instead of feeding it exact dates every year. Please let me know if this is even possible! Thank you for any help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Formula Syntax; If(), Text(), and DateAdd specifically

Hi @Boneckrh19,

 

Please take a try with the format below:

If( 
 Not(IsBlank(ThisItem.HolidayDate)),
 
 Text(DateAdd(ThisItem.HolidayDate, year(Today()) - 1900, Years), "[$-en-US]dddd, mmmm dd, yyyy"), 

 Text(DateAdd(DateAdd(Today(), HolidayMonth - Month(Today()), Months), HolidayNumOfWeeksIn*7 + HolidayDayOfWeek - 1 - Day(Today()), Days),
 "[$-en-US]dddd, mmmm dd, yyyy")
)

The Today() function would give a Date/Time String, we need to take use of the Year(), Month() or the Day() to get the proper single value.

More information, see:

Day, Month, Year, Hour, Minute, Second, and Weekday functions in PowerApps

 

See if that will work, and post back if you have any further questions.

 

Regards,

Michael

Community Support Team _ Michael Shao
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

2 REPLIES 2
Community Support Team
Community Support Team

Re: Formula Syntax; If(), Text(), and DateAdd specifically

Hi @Boneckrh19,

 

Please take a try with the format below:

If( 
 Not(IsBlank(ThisItem.HolidayDate)),
 
 Text(DateAdd(ThisItem.HolidayDate, year(Today()) - 1900, Years), "[$-en-US]dddd, mmmm dd, yyyy"), 

 Text(DateAdd(DateAdd(Today(), HolidayMonth - Month(Today()), Months), HolidayNumOfWeeksIn*7 + HolidayDayOfWeek - 1 - Day(Today()), Days),
 "[$-en-US]dddd, mmmm dd, yyyy")
)

The Today() function would give a Date/Time String, we need to take use of the Year(), Month() or the Day() to get the proper single value.

More information, see:

Day, Month, Year, Hour, Minute, Second, and Weekday functions in PowerApps

 

See if that will work, and post back if you have any further questions.

 

Regards,

Michael

Community Support Team _ Michael Shao
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

Boneckrh19
Level 8

Re: Formula Syntax; If(), Text(), and DateAdd specifically

This appears to work perfectly, except that it made me realize my if statement doesn't work. The four holidays that I never gave date inputs apparently don't count as "blank". They actually contain 1/1/1900 without my having put that in anywhere. So in trying to find a condition that separates the dateless with the dated, I am running into a problem. Is there some kind of special way to compare dates? I already changed the dated ones to use the year 1901 so as not to confuse them, but I'm still having problems.

 

There are no errors that come up here, but apparently neither of these

 

 

If(Not(ThisItem.HolidayDate = 1/1/1900),

If(ThisItem.HolidayDate > 1/1/1900,

 

distinguish between the four that are 1/1/1900 and the four that are not.

Is there something obvious I am missing?

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,298)