Power Platform Community Forum Thread Details

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

Edit: I have come back to this question many years later and realized how underprepared for PowerApps I was at this point! I did not understand DateDiff, DateValue, Year, or even Set. For anyone else who got to this question because of syntax issues, just look up those functions here: https://learn.microsoft.com/en-us/power-platform/power-fx/formula-reference

Categories:

Using Apps Using Formulas

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