Daylight Savings Date Range Expression Examples to...

wskinnermctc · ‎06-09-2022

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:

Background Reasoning
Detail example of building base expression to find Daylight Savings Start and End Date - Example 1
Working example using detail steps to determine if date is within Daylight Savings range - Example 2
Working example using a single expression to determine if date is within Daylight Savings range - Example 3
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 Overview Excel Source Data Table

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

Get March 14th of the year formatted as datetime
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

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

Subtract Day of the Week (0) from March 14th DayOfWeek (5 – 0 = 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

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

Get November 7th of the year formatted as datetime
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

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

Subtract Day of the Week (0) from November 7th DayOfWeek (5 – 0 = 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

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 Detail Example 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 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 p2 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.

Compose String ‘3/14/’
Get the date field you will be using – (my example using excel column Birth Date)
Get the yyyy number from the date field
Concatenate the string ‘3/14/’ and the yyyy to make the 3/14/yyyy and then format it as datetime
Find dayOfWeek for 3/14/yyyy
Subtract the day of week needed (Sunday=0) from the dayOfWeek 3/14/yyyy
Turn the result into a negative number by subtracting the result above from 0
Add Days negative result to 3/14/yyyy to get the start date of daylight savings time
Determine if the Birth Date is before or after daylight savings start date and say ‘before’ or ‘after’
Compose String ‘11/7/’
Concatenate the string ‘11/7/’ and the Birth Date yyyy to make the 11/7/yyyy and format as datetime
Find dayOfWeek for 11/7/yyyy
Subtract the day of week needed (Sunday=0) from dayOfWeek 11/7/yyyy
Turn the result into a negative number by subtracting the result from 0
Add Days negative result to 11/7/yyyy to get the end date of daylight savings time
Determine if the Birth Date is before or after daylight savings end date and say ‘before’ or ‘after’
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 p2 of 6Example 2 Flow Detail p3 of 6Example 2 Flow Detail p4 of 6Example 2 Flow Detail p5 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 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 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 2 Example 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 1 Example 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"}}}