1.
Converting to longer String Fields with the Select button (P1 Start got wierd later on if keeping the small size of the String field).
2. Using the
DateTimeParse function in the formula tool as we want to c
onvert a string field to a date format.-> %m/%d/%Y means that the original format is 1/1/2000 or similar, i.e month number/day number/year
3. Using
Select tool again to
format as Date as this is needed for the next tools.
4. Using the
Generate Rows Tool as I want to see
all the days that is in a specific pay period, as this is needed to calculate how many weekdays it is between.
The generate rows can create all of these days as long as you have a start and an end date which we have.
I created two flows as this was easier, one that using generate rows for P1 and one for P2.
It is really important to use the DateTimeAdd([Date,I,"Dayformat") as the Loop Expression in the end of the Generate rows tool.
5. Use the select tool again to remove all the unneccessary fields.
6. Use the Formula tool again to create the months in the output with
DateTimeFormat function (As Date field to String field).
-> I use the formula DateTimeFormat([PayPeriod],"%B,%Y")
7. Also use formula tool to flag 1 if weekday and 0 if not weekday to be able to sum up later, also uses DateTimeFormat function.
8. Finally sums up all the weekdays per month and Year.
9. Join together the two flows.
10. Join with output to check if everything is correct.