cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wskinnermctc
Super User
Super User

Daylight Savings Date Range Expression Examples to Format Excel Date for SharePoint Upload

This example is used for formatting the start and end date of daylight savings time within a single expression. The examples have multiple steps, but they are only to help understand what needs to go in a single expression. This will be used for setting date ranges for daylight savings time conversions in SharePoint batch uploads from Excel and Dataverse where the time is in ISO 8601 and needs to be converted based on when the date falls during the year.

Foreword: I've only used PA for about 5 months and have never posted anything here before, please let me know if you have any suggestions. I made this because I must make a more complex conversion within an expression in another flow and had to map out the basic steps just so I understood what I needed to put in my complex expression. I hope these examples help people in some form or another when it comes to finding date ranges based on day of week with shifting dates within a single expression. I plan on posting the more complex flow in a second post and then using this post as a reference for details.

 

Outline - This post is in different sections with examples for each:

  1. Background Reasoning
  2. Detail example of building base expression to find Daylight Savings Start and End Date - Example 1
  3. Working example using detail steps to determine if date is within Daylight Savings range - Example 2
  4. Working example using a single expression to determine if date is within Daylight Savings range - Example 3
  5. Conclusion

 

Background Reasoning:

Due to my organization’s setup, I must download data to a CSV from a database, convert the csv to Excel table (or Dataverse), and then upload it to SharePoint. This data is employee information that has historical and personal dates such as employee job hire dates, birth dates, and datetime the CSV was pulled.
The conversion of datetime from Excel during the Power Automate flow changes it into UTC format. When the UTC format is uploaded from the flow to SharePoint, the regional settings of SharePoint convert the datetime to my location US Central Standard Time. Central Standard Time is -6 hour difference from UTC. If the date is during Daylight Savings Time it will adjust the time by an additional hour.

  • Datetime in excel column CSVReportLastUpdate– 5/26/2022 2:38:46 PM
    • Datetime after uploading to SharePoint – 5/26/2022 9:38:46 AM
    • Hour difference 5 = During Daylight Savings Time
  • Datetime in excel column CSVReportLastUpdate – 3/7/2022 2:38:46 PM
    • Datetime after uploading to SharePoint – 3/7/2022 8:38:46 AM
    • Hour difference 6 = During Standard Time (non-Daylight Savings)

I must use conversion formula to adjust the datetime by the amount of hours from UTC to my time zone which is 6 hours during Central Standard Time, but when the dates are in Daylight Savings Time it must be changed by only 5 hours.

To complete the conversion formula, I have to include within the expression a way to check if the date is within the daylight savings time window for the year of the date, and then use the correct hours for conversion.

These examples show the beginning of how to make the expression for using day of week to make date ranges.

Flow OverviewFlow Overview   Excel Source Data TableExcel Source Data Table 

Daylight Savings Date RangeDaylight Savings Date Range

 

Detail Example of Building Base Expression - Example 1

 

The base of this expression will require finding the start and end date of daylight savings time. For US Central Standard time zone, Daylight Savings Time begins on the second Sunday of each March. The end of Daylight Savings Time is the first Sunday of November.

The second Sunday of March is included in Daylight Savings while the first Sunday of November is not included. A date that is the same as the second Sunday in March would need a 5 hour adjustment while a date on the first Sunday of November would need a 6 hour adjustment.

For the year 2008 – daylight savings time began on Sunday March 9, 2008.
For the year 2008 – daylight savings time ended on Sunday November 2, 2008.

  • Dates including and in the range of Sunday March 9, 2008 – Saturday November 1, 2008 would have a 5 hours adjustment. Daylight Savings
  • Dates including and prior Saturday March 10, 2008 and dates including and after Sunday November 2, 2008 will have a 6 hours adjustment. Standard Time

To start the expression we know the maximum start and maximum end date possible each year due to counting days and dates. The start date of Daylight Savings is always on or before March 14th and the end date is always on or before November 7th.

  • (The second Sunday could never be March 15th , the first Sunday could never be November 8th)

All of the steps can be done in a single expression formula for each start and end date but I am breaking each piece of the expression into steps in a flow to give the examples.

Example 1 Flow OverviewExample 1 Flow Overview

 

Steps to complete expression: ( Using March 14, 2008 and November 7, 2008 as initial dates)

*Don't forget the 2 initialized values from beginning of flow not in this scope that contain the dayOfWeek number for start and end days.

  1. Get March 14th of the year formatted as datetime
  2. Get dayOfWeek for March 14th of the year
    • Need the weekday of the week (0 Sunday, 1 Monday, 2 Tuesday, etc.) for March 14th
    • Example March 14, 2008 was a Friday, so this will be 5
  3. Determine day of the week needed. Since we are looking for the Second Sunday my day is Sunday = 0 this will be 0
    • This is contained in the first initialized value at beginning of flow not in this example scope
  4. Subtract Day of the Week (0) from March 14th DayOfWeek  (5 – 0 = 5)
  5. Subtract Result above from 0 to create a negative that will be used in next step (0 – 5 = -5)
    • PowerAutomate doesn't have SubtractDays() so use the AddDays() expression with a negative
  6. Subtract days (which is formula AddDays) from initial start date to get second Sunday date which will be the start date of Daylight Savings for the year.
    • For this example using 2008 Daylight Savings start date will be March 9, 2008

Repeat the steps 1-6 except using November 7th

  1. Get November 7th of the year formatted as datetime
  2. Get dayOfWeek for November 7th of the year
    • Need the weekday of the week (0 Sunday, 1 Monday, 2 Tuesday, etc.) for November 7th
    • Example November 7, 2008 was a Friday, so this will be 5
  3. Determine day of the week needed. Since we are looking for the First Sunday my day is Sunday = 0 this will be 0
    • This is contained in the second initialized value at beginning of flow not in this example scope
  4. Subtract Day of the Week (0) from November 7th DayOfWeek  (5 – 0 = 5)
  5. Subtract Result above from 0 to create a negative that will be used in next step (0 – 5 = -5)
    • PowerAutomate doesn't have SubtractDays() so use the AddDays() expression with a negative
  6. Subtract days (which is formula AddDays) from initial end date to get first Sunday date which will be the end date of Daylight Savings for the year.
    • For this example using 2008 Daylight Savings end date will be November 2, 2008

After following these steps we have the Daylight Savings date range for the year 2008.

Example 1 Flow DetailExample 1 Flow Detail   Example 1 Flow DetailExample 1 Flow Detail

 

The detail steps are using March 14, 2008 as the start date and November 7, 2008 as the end date.

Note: My date range for Daylight Savings only uses Sunday=dayOfWeek=0 for both start and end date; so step A4 and B4 aren't really necessary since it is dayOfWeek-0. This step is left in the example flow so it can be replaced with other days of the week for different flows or date ranges if necessary.

 

Expression to Find Start Date: DaylightSavingsStartDate=addDays(3/14/2008,(0-dayOfWeek(3/14/2008)-dayOfWeek(Sunday=0))

addDays(outputs('Step_A1_Format_March_14th'), sub(0, sub(dayOfWeek(outputs('Step_A1_Format_March_14th')), variables('StartScheduleDayOfWeek'))))

 

Expression to Find Start Date: DaylightSavingsEndDate=addDays(11/7/2008,(0-dayOfWeek(11/7/2008)-dayOfWeek(Sunday=0))

addDays(outputs('Step_B1_Format_November_7th'), sub(0, sub(dayOfWeek(outputs('Step_B1_Format_November_7th')), variables('EndScheduleDayOfWeek'))))

 

 

I added an example array step to see the result of all steps for comparison. The results of Example 1 show how the start and end date of Daylight Savings are found by using a base date of March 14, 2008 and November 7, 2008.

 

The start date of Daylight Savings is the second Sunday of March which for the year 2008 is Sunday March 9. The end date of Daylight Savings is the first Sunday of November which for the year 2008 is Sunday November 2, 2008.

 

Example 1 ResultsExample 1 Results

Using the date range result any date in year 2008 that is Greater Than Or Equal to March 9, 2008 and Less Than November 2, 2008 will be within Daylight Savings date range. (The start date of daylight savings is included while the end date is not included.)

 

Working Example Using Detail Steps - Example 2


I am using the Excel table that has employee birth dates in a column. Even though date only isn't visibly impacted by daylight savings in this example, using simple birth dates in the example allows for a varying range of years to test if the date will be identified correctly as being within Standard Time or if it is in Daylight Savings time.

 

*Don't forget the 2 initialized values from beginning of flow not in this scope that contain the dayOfWeek number for start and end days.

Example 2 Detail p1 of 3Example 2 Detail p1 of 3Example 2 Detail p2 of 3Example 2 Detail p2 of 3Example 2 Detail p3 of 3Example 2 Detail p3 of 3

The Example 2 flow has many steps because it requires the same steps as Example 1 but also includes needing to get the year of the date being used as well as making a comparison of the date to the daylight start date and the daylight end date.

  1. Compose String ‘3/14/’
  2. Get the date field you will be using – (my example using excel column Birth Date)
  3. Get the yyyy number from the date field
  4. Concatenate the string ‘3/14/’ and the yyyy to make the 3/14/yyyy and then format it as datetime
  5. Find dayOfWeek for 3/14/yyyy
  6. Subtract the day of week needed (Sunday=0) from the dayOfWeek 3/14/yyyy
  7. Turn the result into a negative number by subtracting the result above from 0
  8. Add Days negative result to 3/14/yyyy to get the start date of daylight savings time
  9. Determine if the Birth Date is before or after daylight savings start date and say ‘before’ or ‘after’
  10. Compose String ‘11/7/’
  11. Concatenate the string ‘11/7/’ and the Birth Date yyyy to make the 11/7/yyyy and format as datetime
  12. Find dayOfWeek for 11/7/yyyy
  13. Subtract the day of week needed (Sunday=0) from dayOfWeek 11/7/yyyy
  14. Turn the result into a negative number by subtracting the result from 0
  15. Add Days negative result to 11/7/yyyy to get the end date of daylight savings time
  16. Determine if the Birth Date is before or after daylight savings end date and say ‘before’ or ‘after’
  17. Compare the results of both determinations
    • If the Birth Date is before daylight start date it is Central Standard Time
    • If the Birth Date is on or after daylight end date it is Central Standard Time
    • If the Birth Date is not before daylight start date and not on or after or equal to daylight end date it must be in the date range of Daylight Savings

Example 2 Flow Detail p1 of 6Example 2 Flow Detail p1 of 6Example 2 Flow Detail p2 of 6Example 2 Flow Detail p2 of 6Example 2 Flow Detail p3 of 6Example 2 Flow Detail p3 of 6Example 2 Flow Detail p4 of 6Example 2 Flow Detail p4 of 6Example 2 Flow Detail p5 of 6Example 2 Flow Detail p5 of 6Example 2 Flow Detail p6 of 6Example 2 Flow Detail p6 of 6

The initialized variables are the day of the week being search for start and end date. Since both dates are Sunday=dayOfWeek=0 each initialized variable is integer(0). This can easily be inserted into a compose, or skipped since it equals 0, but I initialized the variables for the example use to allow changing the dayOfWeek if needed in another situation.

 

I added an array to the flow to view the results for each apply to each. You can see the flow returned the year for the birth date field and used it for establishing the base dates to determine when start and end dates for daylight savings for each year.

I added an array to the flow to view the results for each apply to each. You can see the flow got the year for the birth date field and used it for establishing the base dates to determine when start and end dates for daylight savings for each year.

Example 2 Results excel row Index 1Example 2 Results excel row Index 1Example 2 Results excel row Index 3Example 2 Results excel row Index 3

For the apply to each result Index 1 the birthdate year was 1984. The second Sunday of March for the year 1984 was March 11, 1984. The first Sunday of November for the year 1984 was November 4, 1984. Since Index 1 employee Thea Kirckx birthday was August 25, 1984 it falls within the Daylight Savings date range.

 

For the apply to each result Index 3 the birthdate year was 1974. The second Sunday of March for the year 1974 was March 10, 1974. The first Sunday of November for the year 1974 was November 3, 1974. Since Index 3 Employee Lynne Tedford birthday was December 24, 1974, it falls after daylight savings end date therefore it is in the Standard Time range.

 

Working Example using a single expression - Example 3

 

To reduce the steps in a flow, the date from the source can be used in a single expression to determine if it is within the Daylight Savings date range. The single expression is long and can be written multiple ways to get the same result. And the expression requires the date be used in multiple areas within the expression.

 

*Don't forget the 2 initialized values from beginning of flow not in this scope that contain the dayOfWeek number for start and end days.

Example 3 Overall Flow ViewExample 3 Overall Flow View

  • The date has the year extracted, to format March 14 and Nov 7
  • The formatted 3/14/yyyy and 11/7/yyyy dates are used to find the start and end dates
  • The date has to be compared twice within an expression to determine where it is positioned between the start and end date
    • The expression can use OR statement to determine if the date is outside of the Daylight Savings range (Date<StartDate Or Date>=EndDate)
    • The expression can use AND statement to determine if the date is within the Daylight Savings range (Date>=StartDate AND Date<EndDate)
    • Whichever statement is used will have a final result that can be determined where the date is located in date ranges

*Don't forget the 2 initialized values from beginning of flow not in this scope that contain the dayOfWeek number for start and end days.

Example 3 Flow Detail p1 of 2Example 3 Flow Detail p1 of 2      Example 3 Flow Detail p2 of 2Example 3 Flow Detail p2 of 2

     

The flow for Example 3 has multiple steps to provide visibility of what is within the expression. However, the Full Expression 1 and Full Expression 2 are stand alone expressions within their own step and do not depend on any other steps (other than the initialize variables for dayOfWeek which could be replaced by simply typing the dayOfWeek number directly into the expression).

 

The Part A and Part B steps show the expressions used to find the startdate and enddate while using the excel date field (Birth Date).

 

Full Expression 1 Date Outside Daylight Range Using OR: 

IF(OR(Less([Date],[StartDate]),GreaterOrEquals([Date],[EndDate])),'StandardTime','DaylightSavings')

if(or(less(items('Apply_to_each')?['Birth Date'], addDays(formatDateTime(concat(string('3/14/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), sub(0, sub(dayofWeek(concat(string('3/14/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), variables('StartScheduleDayOfWeek'))))), greaterOrEquals(items('Apply_to_each')?['Birth Date'], addDays(formatDateTime(concat(string('11/7/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), sub(0, sub(dayofWeek(concat(string('11/7/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), variables('EndScheduleDayOfWeek')))))), 'Standard Time', 'Daylight Savings')

 

Full Expression 2 Date Inside Daylight Range Using AND:

If(AND(GreaterOrEquals([Date],[StartDate]),Less([Date],[EndDate])),'DaylightSavings','StandardTime')

if(and(greaterOrEquals(items('Apply_to_each')?['Birth Date'],addDays(formatDateTime(concat(string('3/14/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),sub(0,sub(dayofWeek(concat(string('3/14/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),variables('StartScheduleDayOfWeek'))))),less(items('Apply_to_each')?['Birth Date'],addDays(formatDateTime(concat(string('11/7/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),sub(0,sub(dayofWeek(concat(string('11/7/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),variables('EndScheduleDayOfWeek')))))),'Daylight Savings', 'Standard Time')

 

 

I added an array to the flow to view the results for each apply to each. You can see the flow got the same results for each Full Expression even though they are written differently.

 

Example 3 Results excel row Index 1Example 3 Results excel row Index 1   Example 3 Results excel row Index 3Example 3 Results excel row Index 3   

 

The results for excel row Index 1 - Thea Dirckx show that the birthdate of Aug-25-1984 comes after the 1984 daylight start date of March 11, 1984 but the birthdate comes before the daylight end date of November 4, 1984. The date of Aug-25-1984 is within Daylight Savings time range.

The results for excel row Index 3 - Lynne Tedford show that the birthdate of Dec-24-1974 comes after the 1974 daylight start date of March 10, 1974 and also after the daylight end date of November 3, 1974. The date of Aug-25-1984 is outside the daylight savings range and is Standard Time.

 

Conclusion:

 

This is a lot of detail for what can be within a single formula. I decided not to get into the issues with hours and time changes in this post because it gets very specific explaining what happened first and then what is needed to correct it. It would become very cluttered trying to explain everything within an expression. I will make another post soon that uses this as a reference and can see where problems come with hours and time conversion. However, for this I hope people are able to see how the dayOfWeek were used and can find some way to apply this to something they can use. 

Using daylight savings as a date range allows a way to make standard dates (3/14, 11/7) to use as a base for determining dayOfWeek. If you are using a different scheduling with no base dates, you will have to find a way to get a base day from another reference such as first of month and then add those additional steps into the expression.

 

Let me know if you have any feedback or suggestions, I will attempt to copy the Example Scopes within the comments below for reference.

 

Scope Example 1: Detail example of building base expression to find Daylight Savings Start and End Date 

copy all code below go to flow add step with clipboard and paste ctrl+v

{"id":"0e8b827d-6d45-42e6-91b8-0e48e5ac4577","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-2f154d0e-584f-4fd8-8b6c-fe21a209fcb4"}}},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Example_1_Basic_Parts_of_Expression","operationDefinition":{"type":"Scope","actions":{"Time_Step_Only_Added_to_allow_Parrallel_Branch_Examples":{"type":"Expression","kind":"CurrentTime","inputs":{},"runAfter":{},"metadata":{"operationMetadataId":"616b4d63-4851-4d18-a048-a2920f05fe71"}},"Step_A2_Find_March14th_DayofWeek":{"type":"Compose","inputs":"@dayOfWeek(outputs('Step_A1_Format_March_14th'))","runAfter":{"Step_A1_Format_March_14th":["Succeeded"]},"description":"Get day of week from initial date. dayOfWeek(outputs('Step_A1_Format_March_14th'))","metadata":{"operationMetadataId":"f293bf94-8937-4791-9f63-56932ef076bb"}},"Step_A3_Set_Schedule_Day_of_Week":{"type":"Compose","inputs":"@variables('StartScheduleDayOfWeek')","runAfter":{"Step_A2_Find_March14th_DayofWeek":["Succeeded"]},"description":"Enter Day of Week being set as an integer (ex 0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday) Can be composed or from variable.","metadata":{"operationMetadataId":"704efb91-383f-4962-aa13-9bbf9fc1a639"}},"Step_A1_Format_March_14th":{"type":"Compose","inputs":"@formatDateTime('3/14/2008')","runAfter":{"Time_Step_Only_Added_to_allow_Parrallel_Branch_Examples":["Succeeded"]},"description":"formatDateTime('3/14/2008')","metadata":{"operationMetadataId":"447a8918-c0c0-4bf5-8713-d060f71fd68d"}},"Step_A4_Subtract_Day_of_Week_from_March14_DayOfWeek":{"type":"Compose","inputs":"@sub(outputs('Step_A2_Find_March14th_DayofWeek'), outputs('Step_A3_Set_Schedule_Day_of_Week'))","runAfter":{"Step_A3_Set_Schedule_Day_of_Week":["Succeeded"]},"description":"Subtract the set day of the week from March 14 DayOfWeek. sub(outputs('Step_A2_Find_March14th_DayofWeek'),outputs('Step_A3_Set_Schedule_Day_of_Week'))","metadata":{"operationMetadataId":"cac17bde-d1a9-4c7a-a7ab-12d0bbb1d371"}},"Step_A5_Subtract_the_Step_4_Result_from_0_to_create_negative":{"type":"Compose","inputs":"@sub(0, outputs('Step_A4_Subtract_Day_of_Week_from_March14_DayOfWeek'))","runAfter":{"Step_A4_Subtract_Day_of_Week_from_March14_DayOfWeek":["Succeeded"]},"description":"Previous Result needs to be negative number or zero so subtract the result from zero. sub(0,outputs('Step_A4_Subtract_Day_of_Week_from_March14_DayOfWeek'))","metadata":{"operationMetadataId":"d18c0cab-28d6-483d-8fb9-d8169aba2240"}},"Step_A6_Add_Negative_Days_to_initial_date_to_get_Schedule_Start_Date":{"type":"Compose","inputs":"@addDays(outputs('Step_A1_Format_March_14th'), outputs('Step_A5_Subtract_the_Step_4_Result_from_0_to_create_negative'))","runAfter":{"Step_A5_Subtract_the_Step_4_Result_from_0_to_create_negative":["Succeeded"]},"description":"AddDays (which is really subtract days when using negative numbers) to the initial date to get the start date. addDays(outputs('Step_A1_Format_March_14th'),outputs('Step_A5_Subtract_the_Step_4_Result_from_0_to_create_negative'))","metadata":{"operationMetadataId":"a8de58a6-8d09-457e-9649-1657662a63a9"}},"All_Steps_A_in_Single_Formula_Using_Format_Date_Step_A1":{"type":"Compose","inputs":"@addDays(outputs('Step_A1_Format_March_14th'), sub(0, sub(dayOfWeek(outputs('Step_A1_Format_March_14th')), variables('StartScheduleDayOfWeek'))))","runAfter":{"Step_A6_Add_Negative_Days_to_initial_date_to_get_Schedule_Start_Date":["Succeeded"]},"description":"This is the formula without all of the additional steps in a single line.","metadata":{"operationMetadataId":"c2c37615-a895-43a9-8d9b-dbbaea643f68"}},"Daylight_Savings_Start_Date_for_Example_View":{"type":"Expression","kind":"ConvertTimeZone","inputs":{"baseTime":"@{outputs('Step_A6_Add_Negative_Days_to_initial_date_to_get_Schedule_Start_Date')}","formatString":"G","sourceTimeZone":"Central Standard Time","destinationTimeZone":"Central Standard Time"},"runAfter":{"All_Steps_A_in_Single_Formula_Using_Format_Date_Step_A1":["Succeeded"]},"metadata":{"operationMetadataId":"b975f949-8bf2-4b72-b25a-ddcfd407df9c"}},"Step_B1_Format_November_7th":{"type":"Compose","inputs":"@formatDateTime('11/7/2008')","runAfter":{"Time_Step_Only_Added_to_allow_Parrallel_Branch_Examples":["Succeeded"]},"description":"formatDateTime('11/7/2008')","metadata":{"operationMetadataId":"606048b2-d0a2-4459-83bf-0e2138aa6638"}},"Step_B2_Find_Nov7th_DayofWeek":{"type":"Compose","inputs":"@dayOfWeek(outputs('Step_B1_Format_November_7th'))","runAfter":{"Step_B1_Format_November_7th":["Succeeded"]},"description":"Get day of week from initial date. dayOfWeek(outputs('Step_A1_Format_November_7th'))","metadata":{"operationMetadataId":"a12c4602-6d14-474b-bb83-34f7e7b29243"}},"Step_B3_Set_Schedule_Day_of_Week":{"type":"Compose","inputs":"@variables('EndScheduleDayOfWeek')","runAfter":{"Step_B2_Find_Nov7th_DayofWeek":["Succeeded"]},"description":"Enter Day of Week being set as an integer (ex 0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday) Can be composed or from variable.","metadata":{"operationMetadataId":"b1d0f4f1-77ae-4bc9-9a9b-9d22607d801c"}},"Step_B4_Subtract_Day_of_Week_from_Nov7_DayOfWeek":{"type":"Compose","inputs":"@sub(outputs('Step_B2_Find_Nov7th_DayofWeek'), outputs('Step_B3_Set_Schedule_Day_of_Week'))","runAfter":{"Step_B3_Set_Schedule_Day_of_Week":["Succeeded"]},"description":"Subtract the set day of the week from Nov 7 DayOfWeek. sub(outputs('Step_B2_Find_Nov7th_DayofWeek'),outputs('Step_B3_Set_Schedule_Day_of_Week'))","metadata":{"operationMetadataId":"15817e14-5110-4319-9493-cb00dbd0ae15"}},"Step_B5_Subtract_the_Step_4_Result_from_0_to_create_negative":{"type":"Compose","inputs":"@sub(0, outputs('Step_B4_Subtract_Day_of_Week_from_Nov7_DayOfWeek'))","runAfter":{"Step_B4_Subtract_Day_of_Week_from_Nov7_DayOfWeek":["Succeeded"]},"description":"Previous Result needs to be negative number or zero so subtract the result from zero. sub(0,outputs('Step_B4_Subtract_Day_of_Week_from_Nov7_DayOfWeek'))","metadata":{"operationMetadataId":"e4fd24d8-2e47-4636-9be2-93feaabb1d12"}},"Step_B6_Add_Negative_Days_to_initial_date_to_get_Schedule_End_Date":{"type":"Compose","inputs":"@addDays(outputs('Step_B1_Format_November_7th'), outputs('Step_B5_Subtract_the_Step_4_Result_from_0_to_create_negative'))","runAfter":{"Step_B5_Subtract_the_Step_4_Result_from_0_to_create_negative":["Succeeded"]},"description":"AddDays (which is really subtract days when using negative numbers) to the initial date to get the end date. addDays(outputs('Step_B1_Format_November_7th'),outputs('Step_B5_Subtract_the_Step_4_Result_from_0_to_create_negative'))","metadata":{"operationMetadataId":"93d91931-5beb-4d5b-b779-d6526aaf06dc"}},"All_Steps_B_in_Single_Formula_Using_Format_Date_Step_B1":{"type":"Compose","inputs":"@addDays(outputs('Step_B1_Format_November_7th'), sub(0, sub(dayOfWeek(outputs('Step_B1_Format_November_7th')), variables('EndScheduleDayOfWeek'))))","runAfter":{"Step_B6_Add_Negative_Days_to_initial_date_to_get_Schedule_End_Date":["Succeeded"]},"description":"This is the formula without all of the additional steps in a single line.","metadata":{"operationMetadataId":"7566a812-8d00-445b-9e71-31951e32e099"}},"Daylight_Savings_End_Date_for_Example_View":{"type":"Expression","kind":"ConvertTimeZone","inputs":{"baseTime":"@{outputs('Step_B6_Add_Negative_Days_to_initial_date_to_get_Schedule_End_Date')}","formatString":"G","sourceTimeZone":"Central Standard Time","destinationTimeZone":"Central Standard Time"},"runAfter":{"All_Steps_B_in_Single_Formula_Using_Format_Date_Step_B1":["Succeeded"]},"metadata":{"operationMetadataId":"2ec799e9-f59a-4751-8d00-33edf4b41c8c"}},"Example_1_Array_Results_for_Viewing":{"type":"AppendToArrayVariable","inputs":{"name":"Example1Array","value":{"Step A1 Format March 14th":"@{outputs('Step_A1_Format_March_14th')}","Step A2 Find March14th DayofWeek":"@{outputs('Step_A2_Find_March14th_DayofWeek')}","Step A3 Set Schedule Day of Week":"@{outputs('Step_A3_Set_Schedule_Day_of_Week')}","Step A4 Subtract Day of Week from March14 DayOfWeek":"@{outputs('Step_A4_Subtract_Day_of_Week_from_March14_DayOfWeek')}","Step A5 Subtract the Step 4 Result from 0 to create negative":"@{outputs('Step_A5_Subtract_the_Step_4_Result_from_0_to_create_negative')}","A6 Add Negative Days to initial date to get Schedule Start Date":"@{outputs('Step_A6_Add_Negative_Days_to_initial_date_to_get_Schedule_Start_Date')}","All Steps A in Single Formula":"@{outputs('All_Steps_A_in_Single_Formula_Using_Format_Date_Step_A1')}","Daylight Savings Start Date":"@{body('Daylight_Savings_Start_Date_for_Example_View')}","Step B1 Format Nov 7th":"@{outputs('Step_B1_Format_November_7th')}","Step B2 Find Nov 7th DayofWeek":"@{outputs('Step_B2_Find_Nov7th_DayofWeek')}","Step B3 Set Schedule Day of Week":"@{outputs('Step_B3_Set_Schedule_Day_of_Week')}","Step B4 Subtract Day of Week from Nov7 DayOfWeek":"@{outputs('Step_B4_Subtract_Day_of_Week_from_Nov7_DayOfWeek')}","Step B5 Subtract Step 4 Result from 0 to create negative":"@{outputs('Step_B5_Subtract_the_Step_4_Result_from_0_to_create_negative')}","B6 Add Negative Days to initial date to get Schedule End Date":"@{outputs('Step_B6_Add_Negative_Days_to_initial_date_to_get_Schedule_End_Date')}","All Steps B in a Single Formula":"@{outputs('All_Steps_B_in_Single_Formula_Using_Format_Date_Step_B1')}","Daylight Savings End Date":"@{body('Daylight_Savings_End_Date_for_Example_View')}"}},"runAfter":{"Daylight_Savings_Start_Date_for_Example_View":["Succeeded"],"Daylight_Savings_End_Date_for_Example_View":["Succeeded"]},"description":"This array is to combine the results of the scope to be easily viewed at once. Not needed for flow to work or work correctly.","metadata":{"operationMetadataId":"696cd6fc-5adb-42d8-89db-e395ad88beb1"}}},"runAfter":{"Set_end_schedule_Day_of_Week":["Succeeded"]},"description":"This scope shows details of how to find the day of week and date needed to start and end a schedule. By looking at these sections you should be able to figure out how to organize your flow and expressions to get schedule dates.","metadata":{"operationMetadataId":"2075ee58-5210-448d-a15e-d25f4957082f"}}}

Scope Example 2: Working example using detail steps to determine if date is within Daylight Savings range

copy all code below go to flow add step with clipboard and paste ctrl+v

{"id":"ecf0beac-b56e-4a47-b393-32d2279ae596","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-2f154d0e-584f-4fd8-8b6c-fe21a209fcb4"}}},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Example_2_Detailed_Date_Range_Format","operationDefinition":{"type":"Scope","actions":{"List_rows_present_in_a_table":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"groups/24550f41-a376-44d2-b23a-20c272c33499","drive":"b!w0EcPolMSEqTajFMTWZ0Bu_B3sLllsVGqewVLusfwXwRsOmpDas-SKu-9piC8VqR","file":"01Q35CSPBAUF25CT65ABAK4LI5D3OG4LM2","table":"{3EA0C0F9-3F11-4088-9569-CE69D6D514F3}","$top":4,"dateTimeFormat":"ISO 8601"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"metadata":{"01Q35CSPBAUF25CT65ABAK4LI5D3OG4LM2":"/General/Excel Test Dates.xlsx","operationMetadataId":"1255349c-5f35-4c83-ab0e-eba133d3e120","tableId":"{3EA0C0F9-3F11-4088-9569-CE69D6D514F3}"}},"Apply_to_Each_BirthDate":{"type":"Foreach","foreach":"@outputs('List_rows_present_in_a_table')?['body/value']","actions":{"Compose_March_14th":{"type":"Compose","inputs":"@string('3/14/')","runAfter":{},"description":"Compose the first part of the date 3/14/ without the year.  string('3/14/')","metadata":{"operationMetadataId":"e00b0a65-fc36-4f8c-a014-6273c99e8385"}},"Get_the_yyyy_from_date":{"type":"Compose","inputs":"@formatDateTime(items('Apply_to_Each_BirthDate')?['Birth Date'], 'yyyy')","runAfter":{"Get_date_from_field":["Succeeded"]},"description":"Get the year only from the date field being used. formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy')","metadata":{"operationMetadataId":"029595e0-3667-478b-9d00-f158d5f8c928"}},"Format_March_14_yyyy":{"type":"Compose","inputs":"@formatDateTime(concat(outputs('Compose_March_14th'), outputs('Get_the_yyyy_from_date')))","runAfter":{"Get_the_yyyy_from_date":["Succeeded"]},"description":"Format 3/14/yyyy using the year from the field. formatDateTime(concat(outputs('Compose_March_14th'), outputs('Get_the_yyyy_from_date')))","metadata":{"operationMetadataId":"8c710b5d-1328-42b1-814b-2f270d023514"}},"Get_date_from_field":{"type":"Compose","inputs":"@items('Apply_to_Each_BirthDate')?['Birth Date']","runAfter":{"Compose_March_14th":["Succeeded"]},"description":"This is the date field that is being used.","metadata":{"operationMetadataId":"75baf351-df71-4c7f-b1f7-4661506d7afd"}},"Compose_Nov_7th":{"type":"Compose","inputs":"@string('11/7/')","runAfter":{"Determine_Date_Before_or_After_Daylight_Savings_Start_Date":["Succeeded"]},"description":"compose the first part of the date 11/7/ without the year. string('11/7/')","metadata":{"operationMetadataId":"42c2b688-f36a-4ac0-a109-3160913826f4"}},"Format_Nov_7th_yyyy":{"type":"Compose","inputs":"@formatDateTime(concat(outputs('Compose_Nov_7th'), outputs('Get_the_yyyy_from_date')))","runAfter":{"Compose_Nov_7th":["Succeeded"]},"description":"Format 11/7/yyyy using the year from the get date field. formatDateTime(concat(outputs('Compose_Nov_7th'),outputs('Get_the_yyyy_from_date')))","metadata":{"operationMetadataId":"05ea8ea8-ce66-4fa1-9c35-a25abe7d1a46"}},"Determine_Date_is_Before_or_After_Daylight_Savings_End_Date":{"type":"Compose","inputs":"@if(greater(items('Apply_to_Each_BirthDate')?['Birth Date'], outputs('Add_Negative_Days_to_Nov7th_to_get_Daylight_Savings_End_Date')), 'after', 'before')","runAfter":{"Add_Negative_Days_to_Nov7th_to_get_Daylight_Savings_End_Date":["Succeeded"]},"description":"Compare the birthdate to daylight savings end date to see if it is before or after. if(greater(items('Apply_to_Each_BirthDate')?['Birth Date'],outputs('Add_Negative_Days_to_Nov7th_to_get_Daylight_Savings_End_Date')),'after','before')","metadata":{"operationMetadataId":"c6f26da3-0d00-4fa8-8ef1-09dafc029266"}},"Compare_if_date_is_in_Standard_Time_or_Daylight_Savings_Time":{"type":"If","expression":{"or":[{"equals":["@outputs('Determine_Date_Before_or_After_Daylight_Savings_Start_Date')","before"]},{"equals":["@outputs('Determine_Date_is_Before_or_After_Daylight_Savings_End_Date')","after"]}]},"actions":{"Show_if_Standard_Time_Range":{"type":"Expression","kind":"ConvertTimeZone","inputs":{"baseTime":"@items('Apply_to_Each_BirthDate')?['Birth Date']","formatString":"G","sourceTimeZone":"Central Standard Time","destinationTimeZone":"Central Standard Time"},"runAfter":{},"metadata":{"operationMetadataId":"3e856247-22f0-4aca-a3df-b04621e52a04"}}},"runAfter":{"Determine_Date_is_Before_or_After_Daylight_Savings_End_Date":["Succeeded"]},"else":{"actions":{"Show_if_Daylight_Savings_Time":{"type":"Expression","kind":"ConvertTimeZone","inputs":{"baseTime":"@items('Apply_to_Each_BirthDate')?['Birth Date']","formatString":"G","sourceTimeZone":"Central Standard Time","destinationTimeZone":"Central Standard Time"},"runAfter":{},"metadata":{"operationMetadataId":"5966b854-660f-43ec-9b48-cd0a31a61d56"}}}},"description":"Use both determine step outputs to say if the date is before or after daylight start date and before or after daylight end. If it is before or after it must be Standard Time and not Daylight Savings","metadata":{"operationMetadataId":"73d69ebc-be59-4070-8b8b-1dbe1ebe65b9"}},"Determine_Date_Before_or_After_Daylight_Savings_Start_Date":{"type":"Compose","inputs":"@if(greater(items('Apply_to_Each_BirthDate')?['Birth Date'], outputs('Add_Negative_Days_March14yyyy_to_get_DaylightSavings_Start_Date')), 'after', 'before')","runAfter":{"Add_Negative_Days_March14yyyy_to_get_DaylightSavings_Start_Date":["Succeeded"]},"description":"Compare the birth date to Daylight StartDate to see if it is before or after. if(greater(items('Apply_to_Each_BirthDate')?['Birth Date'],outputs('Add_Negative_days_to_initial_date_to_get_DaylightSavings_Start_Date')),'after','before')","metadata":{"operationMetadataId":"7dd64cc7-882f-482e-ad5b-c08f1273500d"}},"Find_March14th_DayofWeek":{"type":"Compose","inputs":"@dayOfWeek(outputs('Format_March_14_yyyy'))","runAfter":{"Format_March_14_yyyy":["Succeeded"]},"description":"Get day of week from formatted March 14th. dayOfWeek(outputs('Format_March_14_yyyy'))","metadata":{"operationMetadataId":"d67d002e-81ab-4817-91d3-aa00dd7a9d2b"}},"Subtract_Day_of_Week_from_March14_DayOfWeek":{"type":"Compose","inputs":"@sub(outputs('Find_March14th_DayofWeek'), variables('StartScheduleDayOfWeek'))","runAfter":{"Find_March14th_DayofWeek":["Succeeded"]},"description":"Subtract the set day of the week from March 14 DayOfWeek. sub(outputs('Find_March_14th_DayofWeek),variables('StartScheduleDayOfWeek'))","metadata":{"operationMetadataId":"3bc5359f-ea00-4109-b923-7d40c362be35"}},"Subtract_the_Previous_Step_Result_from_0_to_create_a_negative":{"type":"Compose","inputs":"@sub(0, outputs('Subtract_Day_of_Week_from_March14_DayOfWeek'))","runAfter":{"Subtract_Day_of_Week_from_March14_DayOfWeek":["Succeeded"]},"description":"Previous results needs to be negative number or zero to use in next AddDays formula. sub(0,outputs('Subtract_Day_of_Week_From_March14_DayOfWeek'))","metadata":{"operationMetadataId":"8e7eecbe-a823-4129-a610-ab18d43241ae"}},"Add_Negative_Days_March14yyyy_to_get_DaylightSavings_Start_Date":{"type":"Compose","inputs":"@addDays(outputs('Format_March_14_yyyy'), outputs('Subtract_the_Previous_Step_Result_from_0_to_create_a_negative'))","runAfter":{"Subtract_the_Previous_Step_Result_from_0_to_create_a_negative":["Succeeded"]},"description":"AddDays (which is really subtract days when using negative numbers) to the formatted March 14th to get the Daylight Savings Start Date. addDays(","metadata":{"operationMetadataId":"61a4ba33-fe9e-47f9-8373-e6abf4f30284"}},"Find_November7th_DayofWeek":{"type":"Compose","inputs":"@dayOfWeek(outputs('Format_Nov_7th_yyyy'))","runAfter":{"Format_Nov_7th_yyyy":["Succeeded"]},"description":"Get day of week from formatted Nov 7th yyyy. dayOfWeek(outputs('Format_March_7th_yyyy'))","metadata":{"operationMetadataId":"29893ebd-ea8e-4866-a47c-ecd9d5bc33eb"}},"Subtract_Day_of_Week_from_Nov7th_DayOfWeek":{"type":"Compose","inputs":"@sub(outputs('Find_November7th_DayofWeek'), variables('EndScheduleDayOfWeek'))","runAfter":{"Find_November7th_DayofWeek":["Succeeded"]},"description":"Subtract the set day of the week from Nov7th DayOfWeek. sub(outputs('Find_November7th_DayofWeek),variables('EndScheduleDayOfWeek'))","metadata":{"operationMetadataId":"596144eb-5ca4-462e-be01-128caec6d005"}},"Add_Negative_Days_to_Nov7th_to_get_Daylight_Savings_End_Date":{"type":"Compose","inputs":"@addDays(outputs('Format_Nov_7th_yyyy'), outputs('Subtract_the_Previous_Step_Result_from_0_to_create_a_negative_int'))","runAfter":{"Subtract_the_Previous_Step_Result_from_0_to_create_a_negative_int":["Succeeded"]},"description":"AddDays (which is really subtract days when using negative numbers) to the formatted Nov7yyyy to get the Daylight Savings Start Date. addDays(outputs('Format_Nov_7th_yyyy'),outputs('Subtract_the_Previous_Step_Result_from_0_to_create_a_negative_int'))","metadata":{"operationMetadataId":"eba71582-0f53-4079-9f47-12ed05917c89"}},"Subtract_the_Previous_Step_Result_from_0_to_create_a_negative_int":{"type":"Compose","inputs":"@sub(0, outputs('Subtract_Day_of_Week_from_Nov7th_DayOfWeek'))","runAfter":{"Subtract_Day_of_Week_from_Nov7th_DayOfWeek":["Succeeded"]},"description":"Previous results needs to be negative number or zero to use in next AddDays formula. sub(0,outputs('Subtract_Day_of_Week_From_Nov7th_DayOfWeek'))","metadata":{"operationMetadataId":"acdf2fe8-a095-4db7-95e1-fb8437a29890"}},"Example_2_Results_Array_for_Viewing":{"type":"AppendToArrayVariable","inputs":{"name":"Example2Array","value":{"Index":"@{items('Apply_to_Each_BirthDate')?['Index']}","EmployeeName":"@{items('Apply_to_Each_BirthDate')?['Emp Name']}","ComposeMarch14":"@{outputs('Compose_March_14th')}","Get date from field":"@{outputs('Get_date_from_field')}","Get the yyyy from date":"@{outputs('Get_the_yyyy_from_date')}","Format March 14 yyyy":"@{outputs('Format_March_14_yyyy')}","Find March14th DayofWeek":"@{outputs('Find_March14th_DayofWeek')}","Subtract Day of Week from March14 DayOfWeek":"@{outputs('Subtract_Day_of_Week_from_March14_DayOfWeek')}","Subtract the Previous Step Result from 0 to create a negative":"@{outputs('Subtract_the_Previous_Step_Result_from_0_to_create_a_negative')}","Add Negative Days March14yyyy to get DaylightSavingsStart Date":"@{outputs('Add_Negative_Days_March14yyyy_to_get_DaylightSavings_Start_Date')}","Determine Date Before or After Daylight Savings Start Date":"@{outputs('Determine_Date_Before_or_After_Daylight_Savings_Start_Date')}","Compose Nov 7th":"@{outputs('Compose_Nov_7th')}","Format Nov 7th yyyy":"@{outputs('Format_Nov_7th_yyyy')}","Find November7th DayofWeek":"@{outputs('Find_November7th_DayofWeek')}","Subtract Day of Week from Nov7th DayOfWeek":"@{outputs('Subtract_Day_of_Week_from_Nov7th_DayOfWeek')}","Subtract the Previous Step Result from 0 to create a negative int":"@{outputs('Subtract_the_Previous_Step_Result_from_0_to_create_a_negative_int')}","Add Negative Days to Nov7th to get Daylight Savings End Date":"@{outputs('Add_Negative_Days_to_Nov7th_to_get_Daylight_Savings_End_Date')}","Determine if Date is Before or After Daylight Savings End Date":"@{outputs('Determine_Date_is_Before_or_After_Daylight_Savings_End_Date')}"}},"runAfter":{"Compare_if_date_is_in_Standard_Time_or_Daylight_Savings_Time":["Succeeded"]},"description":"This array is added to see all the example results at once. It is not needed for flow to work correctly.","metadata":{"operationMetadataId":"1bd9744d-39a0-413d-b49c-8e3e3412aa77"}}},"runAfter":{"List_rows_present_in_a_table":["Succeeded"]},"metadata":{"operationMetadataId":"ad7b0b71-6ad7-4696-be0e-011289416802"}}},"runAfter":{"Set_end_schedule_Day_of_Week":["Succeeded"]},"description":"This is a detailed example using an excel table that shows how single dates are compared with the schedule start and end times.","metadata":{"operationMetadataId":"23e9a10d-9948-4834-be21-71af6a7f1411"}}}

Scope Example 3: Working example using a single expression to determine if date is within Daylight Savings range

copy all code below go to flow add step with clipboard and paste ctrl+v

{"id":"c8cf87e4-2c9b-420f-a3ed-575a0691d387","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-2f154d0e-584f-4fd8-8b6c-fe21a209fcb4"}}},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Example_3_Single_Expression_Date_Range","operationDefinition":{"type":"Scope","actions":{"List_rows_present_in_a_table_example_3":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"groups/24550f41-a376-44d2-b23a-20c272c33499","drive":"b!w0EcPolMSEqTajFMTWZ0Bu_B3sLllsVGqewVLusfwXwRsOmpDas-SKu-9piC8VqR","file":"01Q35CSPBAUF25CT65ABAK4LI5D3OG4LM2","table":"{3EA0C0F9-3F11-4088-9569-CE69D6D514F3}","$top":4,"dateTimeFormat":"ISO 8601"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"metadata":{"01Q35CSPBAUF25CT65ABAK4LI5D3OG4LM2":"/General/Excel Test Dates.xlsx","operationMetadataId":"a6512af8-4d53-4455-adf0-1501146bd925","tableId":"{3EA0C0F9-3F11-4088-9569-CE69D6D514F3}"}},"Apply_to_each":{"type":"Foreach","foreach":"@outputs('List_rows_present_in_a_table_example_3')?['body/value']","actions":{"Expression_Part_A_Daylight_Start_Date":{"type":"Compose","inputs":"@addDays(formatDateTime(concat(string('3/14/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), sub(0, sub(dayofWeek(concat(string('3/14/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), variables('StartScheduleDayOfWeek'))))","runAfter":{},"description":"This is the written expression that will find the Daylight Savings Start date based on the yyyy from the individual date being used","metadata":{"operationMetadataId":"c21af95b-7afb-4884-92d2-fe76e1f664c5"}},"Expression_Part_B_Daylight_End_Date":{"type":"Compose","inputs":"@addDays(formatDateTime(concat(string('11/7/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), sub(0, sub(dayofWeek(concat(string('11/7/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), variables('EndScheduleDayOfWeek'))))","runAfter":{"Expression_Part_A_Daylight_Start_Date":["Succeeded"]},"description":"This is the written expression that will find the Daylight Savings End date based on the yyyy from the individual date being used","metadata":{"operationMetadataId":"6091bda8-ceff-4f47-a5c4-0ed339543711"}},"General_Expression_Comparing_Date_to_Daylight_Start_Date":{"type":"Compose","inputs":"@if(less(items('Apply_to_each')?['Birth Date'], outputs('Expression_Part_A_Daylight_Start_Date')), 'before', 'after')","runAfter":{"Expression_Part_B_Daylight_End_Date":["Succeeded"]},"description":"This expression compares the date being used with the Daylight Start Date and determines if it is before or after.","metadata":{"operationMetadataId":"6a486cdc-99a4-48b4-a426-714effef416d"}},"General_Expression_Comparing_Date_to_Daylight_End_Date":{"type":"Compose","inputs":"@if(greaterOrEquals(items('Apply_to_each')?['Birth Date'], outputs('Expression_Part_B_Daylight_End_Date')), 'after', 'before')","runAfter":{"General_Expression_Comparing_Date_to_Daylight_Start_Date":["Succeeded"]},"description":"This expression compares the date being used with Daylight End Date and determines if it is before or equal to and after","metadata":{"operationMetadataId":"94ee67ca-9dd6-42ee-b40e-cbf976aa5a54"}},"Full_Expression_2_Date_Inside_Daylight_Range_using_AND":{"type":"Compose","inputs":"@if(and(greaterOrEquals(items('Apply_to_each')?['Birth Date'],addDays(formatDateTime(concat(string('3/14/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),sub(0,sub(dayofWeek(concat(string('3/14/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),variables('StartScheduleDayOfWeek'))))),less(items('Apply_to_each')?['Birth Date'],addDays(formatDateTime(concat(string('11/7/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),sub(0,sub(dayofWeek(concat(string('11/7/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),variables('EndScheduleDayOfWeek')))))),'Daylight Savings', 'Standard Time')","runAfter":{"Full_Expression_1_Date_Outside_Daylight_Range_using_OR":["Succeeded"]},"description":"Uses AND statement to say If the date is greaterOrEqual to Daylight Start Date AND date is lessthan Daylight End Date then it is 'Daylight Savings' otherwise it is 'Standard Time'"},"Full_Expression_1_Date_Outside_Daylight_Range_using_OR":{"type":"Compose","inputs":"@if(or(less(items('Apply_to_each')?['Birth Date'], addDays(formatDateTime(concat(string('3/14/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), sub(0, sub(dayofWeek(concat(string('3/14/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), variables('StartScheduleDayOfWeek'))))), greaterOrEquals(items('Apply_to_each')?['Birth Date'], addDays(formatDateTime(concat(string('11/7/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), sub(0, sub(dayofWeek(concat(string('11/7/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), variables('EndScheduleDayOfWeek')))))), 'Standard Time', 'Daylight Savings')","runAfter":{"General_Expression_Comparing_Date_to_Daylight_End_Date":["Succeeded"]},"description":"Uses Or statement to say If the date is less than Daylight Start Date Or date is greaterOrEqual to Daylight End Date then it is 'Standard Time' otherwise it is 'Daylight Savings'","metadata":{"operationMetadataId":"6b369ec5-9925-4b66-86ee-7189f1a803d5"}},"Example_3_Array_for_Results_Viewing":{"type":"AppendToArrayVariable","inputs":{"name":"Example3Array","value":{"Index":"@{items('Apply_to_each')?['Index']}","Employee Name":"@{items('Apply_to_each')?['Emp Name']}","Date from Excel Field":"@{formatDateTime(items('Apply_to_each')?['Birth Date'], 'MMM-dd-yyyy hh:mm:ss')}","Expression Part A Daylight Start Date":"@{formatDateTime(outputs('Expression_Part_A_Daylight_Start_Date'),'MMM-dd-yyyy hh:mm:ss')}","Expression Part B Daylight End Date":"@{formatDateTime(outputs('Expression_Part_B_Daylight_End_Date'),'MMM-dd-yyyy hh:mm:ss')}","General Comparing Date to Daylight Start Date":"@{outputs('General_Expression_Comparing_Date_to_Daylight_Start_Date')}","General Comparing Date to Daylight End Date":"@{outputs('General_Expression_Comparing_Date_to_Daylight_End_Date')}","Full Expression 1 Date Outside Daylight Range OR":"@{outputs('Full_Expression_1_Date_Outside_Daylight_Range_using_OR')}","Full Expression 1 Details":"@{if(or(less(items('Apply_to_each')?['Birth Date'], addDays(formatDateTime(concat(string('3/14/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), sub(0, sub(dayofWeek(concat(string('3/14/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), variables('StartScheduleDayOfWeek'))))), greaterOrEquals(items('Apply_to_each')?['Birth Date'], addDays(formatDateTime(concat(string('11/7/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), sub(0, sub(dayofWeek(concat(string('11/7/'), formatDateTime(items('Apply_to_each')?['Birth Date'], 'yyyy'))), variables('EndScheduleDayOfWeek')))))), 'Outside CDT Range true(ExcelDate<StartDate or ExcelDate>=EndDate)', 'Outside CDT Range false(ExcelDate<StartDate or ExcelDate>=EndDate)')}","Full Expression 2 Date Inside Daylight Range AND":"@{outputs('Full_Expression_2_Date_Inside_Daylight_Range_using_AND')}","Full Expression 2 Details":"@{if(and(greaterOrEquals(items('Apply_to_each')?['Birth Date'],addDays(formatDateTime(concat(string('3/14/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),sub(0,sub(dayofWeek(concat(string('3/14/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),variables('StartScheduleDayOfWeek'))))),less(items('Apply_to_each')?['Birth Date'],addDays(formatDateTime(concat(string('11/7/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),sub(0,sub(dayofWeek(concat(string('11/7/'),formatDateTime(items('Apply_to_each')?['Birth Date'],'yyyy'))),variables('EndScheduleDayOfWeek')))))),'Inside CDT Range true(ExcelDate>=StartDate AND ExcelDate<EndDate)', 'Inside CDT Range false(ExcelDate>=StartDate AND ExcelDate<EndDate)')}"}},"runAfter":{"Full_Expression_2_Date_Inside_Daylight_Range_using_AND":["Succeeded"]},"description":"This array is to see the results of Example 3 all at once. This is not needed for flow to work.","metadata":{"operationMetadataId":"8ffd8bca-6f45-4052-874b-6d6e1d99cefb"}}},"runAfter":{"List_rows_present_in_a_table_example_3":["Succeeded"]},"metadata":{"operationMetadataId":"9fa5f29e-a2f3-4a75-bafa-cb486d1beef8"}}},"runAfter":{"Set_end_schedule_Day_of_Week":["Succeeded"]},"description":"This shows how a single expression can be used to determine if a date is within a schedule range. There are two full example expressions that have same results but written differently.","metadata":{"operationMetadataId":"bc0c8ad9-da41-411d-8dae-ca12819a4480"}}}
0 REPLIES 0

Helpful resources

Announcements

Celebrating a New Season of Super Users with Charles Lamanna, CVP Microsoft Business Applications

February 8 was the kickoff to the 2024 Season One Super User program for Power Platform Communities, and we are thrilled to welcome back so many returning Super Users--as well as so many brand new Super Users who started their journey last fall. Our Community Super Users are the true heroes, answering questions, providing solutions, filtering spam, and so much more. The impact they make on the Communities each day is significant, and we wanted to do something special to welcome them at our first kickoff meeting of the year.   Charles Lamanna, Microsoft CVP of Business Applications, has stressed frequently how valuable our Community is to the growth and potential of Power Platform, and we are honored to share this message from him to our 2024 Season One Super Users--as well as anyone who might be interested in joining this elite group of Community members.     If you want to know more about Super Users, check out these posts for more information today:    Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

February 2024 User Group Update: Welcoming New Groups and Highlighting Upcoming Events

It's a new month and a brand-new year, which means another opportunity to celebrate our amazing User Groups!Each month, we highlight the new User Groups that have joined the community. It's been a busy season for new groups, because we are thrilled to welcome 15 New User Groups! Take a look at the list below, shared by the different community categories. If your group is listed here, give this post a kudo so we can celebrate with you!   We love our User Groups and the difference they make in the lives of our Community! Thank you to all the new User Groups, new User Group leaders--we look forward to hearing about your successes and the impact you will leave!   In addition to our monthly User Group spotlight, it's a great time to share some of the latest events happening in our User Group community! Take a look at the list below to find one that fits your schedule and need! There's a great combination of in-person and virtual events to choose from. Also, don't forget to review the many events happening near you or virtually! It's a great time of year to connect and engage with User Groups both locally and online. Please Welcome Our NEW User Groups   Power Platform: Heathcare Power Platform User Group Power Platform Connect Hub Power Platform Usergroup Denmark Mexico Norte- Power Platform User Group Pune Power User Group Sudbury Power Platform User GroupMicrosoft User Group GhanaMPPBLR - Microsoft Power Platform Bengaluru User Group Power Apps:   Myrtle Beach Power Platform User GroupAnanseTechWB PowerApps Copilot Studio: Pathfinders Power Platform Community Dynamics365: Cairo, Egypt MSD 365 Business Central/NAV/F&O User GruopMS Dynamics 365 Business Central LatamCincinnati OH D365 F&O User Group February User Group Events February 2024 Cleveland Power Platform User GroupPortallunsj - Februar 2024Indiana D365/AX February User Group MeetingQ1 2024 KC Power Platform and Dynamics 365 CRM Users Group 

January 2024 Community Newsletter

Welcome to our January Newsletter, where we highlight the latest news, product releases, upcoming events, and the amazing work of our outstanding Community members. If you're new to the Community, please make sure to follow the latest News & Announcements in each Community and check out the Community on LinkedIn as well! It's the best way to stay up-to-date in 2024 with all the news from across Microsoft Power Platform and beyond.      COMMUNITY HIGHLIGHTS Check out the most active community members of the last month! These hardworking members are posting regularly, answering questions, giving (and receiving!) kudos, and consistently providing top solutions in their communities. We are so thankful for each of you--keep up the great work! If you hope to see your name here next month, make it your New Year's Resolution to be more active in the community in 2024.   Power AppsPower AutomateCopilot StudioPower PagesWarrenBelzWarrenBelzPstork1saudali_25LaurensMPstork1stephenrobertLucas001AARON_ClbendincpaytonSurendran_RANBNived_NambiarMariamPaulachanNikhil2JmanriqueriosANBJupyter123rodger-stmmbr1606Agniusstevesmith27mandelaPhineastrice602AnnaMoyalanOOlashynBCLS776grantjenkinsExpiscornovusJcookSpongYeAARON_CManishSolankiapangelesPstork1ManishSolankiSanju1Fubar   LATEST NEWS Power Platform 2024 Release Wave Highlights This month saw the 2024 Release Wave 1 plans for Microsoft Power Platform and Microsoft Dynamics 365- a compilation of new capabilities planned for release between April and September 2024. Click here to read Corporate Vice President Maureen (Mo) Osborne's detailed breakdown of the upcoming capabilities, and click the image below to check out some of the Power Platform 2024 Release Wave 1 highlights.     "What's New" Power Platform Shorts Series This month we also launched our brand-new 'Power Shorts' series on YouTube - a selection of super sweet snapshots to keep you in the loop with all the latest trends from across the Power Platform and beyond. Click the image below to check out the entire playlist so far, and don't forget to subscribe to our YouTube channel for all the latest updates.   Super User In Training (S.U.I.T) It was great to see the Power Platform Community officially kick off Season One of their Super User in Training (SUIT) program this month! Their first meeting saw an amazing turnout of over 300 enthusiastic participants who started their dynamic journey toward becoming Super Users. Huge thanks to Manas Maheshwari, Eric Archer, Heather Hernandez, and Duane Montague for a fantastic kick-off. The first meeting also saw seasoned Super User, Drew Poggemann, share invaluable insights on navigating the #MicrosoftCommunity with finesse. Many thanks to Drew for setting the stage and emphasizing the importance of active engagement and the art of providing thoughtful community solutions. If you want to learn more about the features and benefits of gaining Super User status, click the image below to find out more, and watch this space for more info about Season Two and how you can SUIT UP in the community!     UPCOMING EVENTS Microsoft 365 Community Day - Miami - February 1-2, 2024 It's not long now until the Microsoft 365 Community Day Miami, which will be taking place at the Wolfson Campus at Miami Dade College on 1-2 Feb. 2024. This free event is all about unlocking the full potential of Power Platform, Microsoft 365, and AI, so whether you’re a tech enthusiast, a business owner, or just curious about the latest Microsoft advancements, #M365Miami is for you.   The event is completely free and there will sessions in both English and Spanish to celebrate the vibrant and diverse make-up of our amazing community. Click the image below to join this amazing Community Day in Miami and become a part of our incredible network of learners and innovators!     Microsoft Fabric - Las Vegas - March 26-28, 2024 Exciting times ahead for the inaugural #MicrosoftFabric Community Conference on March 26-28 at the MGM Grand in Las Vegas. And if you book now, you can save $100 off registration! The Microsoft Fabric Conference will cover all the latest in analytics, AI, databases, and governance across 150+ sessions.   There will be a special Community Lounge onsite, interactive learning labs, plus you'll be able to 'Ask the Experts' all your questions to get help from data, analytics, and AI specialists, including community members and the Fabric Customer Advisory Team. Just add the code MSCUST when registering for a $100 discount today. Click the image below to find out more about the ultimate learning event for Microsoft Fabric!     Microsoft 365 Conference - Orlando - April 30 - May 2, 2024 Have you added The Microsoft 365 Conference to your community calendar yet? It happens this April 30th - May 2nd in Orlando, Florida. The 2024 Microsoft 365 Conference is one of the world’s largest gatherings of Microsoft engineers and community, with a strong focus on Power Platform, SharePoint, Azure, and the transition to an AI-powered modern workplace.   Click the image link below to find out more and be prepared to be enlightened, educated, and inspired at #M365Conf24!   LATEST COMMUNITY BLOG ARTICLES Power Apps Community Blog Power Automate Community Blog Copilot Studio Community Blog Power Pages Community Blog Check out 'Using the Community' for more helpful tips and information: Power Apps, Power Automate, Copilot Studio, Power Pages  

Super Users 2024 Season One is Here!

   We are excited to announce the first season of our 2024 Super Users is here! Our kickoff to the new year welcomes many returning Super Users and several new faces, and it's always exciting to see the impact these incredible individuals will have on the Community in 2024! We are so grateful for the daily difference they make in the Community already and know they will keep staying engaged and excited for all that will happen this year.   How to Spot a Super User in the Community:Have you ever written a post or asked for help in the Community and had it answered by a user with the Super User icon next to their name? It means you have found the actual, real-life superheroes of the Power Platform Community! Super Users are our heroes because of the way they consistently make a difference in the Community. Our amazing Super Users help keep the Community a safe place by flagging spam and letting the Community Managers know about issues. They also make the Community a great place to find answers, because they are often the first to offer solutions and get clarity on questions. Finally, Super Users share valuable insights on ways to keep the Community growing, engaging, and looking ahead!We are honored to reveal the new badges for this season of Super Users! Congratulations to all the new and returning Super Users!     To better answer the question "What is a Super User?" please check out this article: Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

Did You Attend the Microsoft Power Platform Conference in 2022 or 2023? Claim Your Badge Today!

If you were one of the thousands of people who joined us at the first #MPPC Microsoft Power Platform Conference in 2022 in Orlando--or attended the second-annual conference in Las Vegas in 2023--we are excited to honor you with a special community badge! Show your support for #MPPC Microsoft Power Platform Conference this year by claiming your badge!           Just follow this link to claim your badge for attending #MPPC in 2022 and/or 2023: MPPCBadgeRequest    Want to earn your badge for 2024? Just keep watching our News & Announcements for the latest updates on #MPPC24.

Microsoft Power Platform | 2024 Release Wave 1 Plan

Check out the latest Microsoft Power Platform release plans for 2024!   We have a whole host of exciting new features to help you be more productive, enhance delegation, run automated testing, build responsive pages, and so much more.    Click the links below to see not only our forthcoming releases, but to also try out some of the new features that have recently been released to market across:     Power Apps  Power Automate  Copilot Studio   We can’t wait to share with you all the upcoming releases that will help take your Power Platform experience to the next level!    Check out the entire Release Wave: Power Platform Complete Release Planner 

Users online (4,167)