Generate date columns

[hcao]

how can i transform this:

Person	Role start date	Role end date	Effective date	Effective end date	Pay
1	2014-12-01	2021-01-29	2017-07-01	2020-06-30	30
1	2014-12-01	2021-01-29	2020-07-01	2021-01-01	31
1	2014-12-01	2021-01-29	2021-01-02	2021-01-07	32

into this:

Person	Start	End	Pay
1	2014-12-01	2020-06-30	30
1	2020-07-01	2021-01-01	31
1	2021-01-02	2021-01-29	32

the [Start] will always be [Role start date], and the [End] will always be [Role end date] but I want to incorporate the effective dates in as well.

[Yoshiro_Fujimori · Accepted solution]

Hi @hcao ,

Is this what you mean?

If you have multiple [Person], you may want to use Multi-Row Formula tool instead of Record ID tool.

Workflow

Formula Tool

Start = IF [RecordID] = 1 THEN [Role start date] ELSE [Effective date] ENDIF

End = IF [RecordID] = [Count] THEN [Role end date] ELSE [Effective end date] ENDIF

[hcao]

Thank you, yes that works perfectly. Just want to ask how would you use the multi-row formula? I do have multiple [Person]

[Yoshiro_Fujimori]

Hi @hcao ,

In case you have multiple [Person], you need to give the record ID for each [Person], and count the rows for each [Person].

So the workflow needs to be updated as below;

(Please check the configuration of Multi-Row Formula, Summarize, Join tools)

Workflow