Consolidate dates to date ranges

[drckarcher]

Hi,

I've got an old database application that recorded staff roles by date. For each month of a staff member's employment, a role was recorded as well as the date as YYYY-MM.

This is what the data looks like:

Lastname		Position	Start Date
Homer		CEO	2010-07
Homer		CEO	2010-06
Homer		COO	2010-05
Homer		COO	2010-04
Homer		COO	2010-03
Homer		CFO	2010-02

 

In the new CASPIO application, roles are recorded with a start and end date.

What is the easiest way to transform the old format to the new one and consolidate so that it looks like the table below?

Lastname	Position	Start Date	End Date
Homer	CEO	01.06.2010	31.07.2010
Homer	COO	01.03.2010	31.05.2010
Homer	CFO	01.02.2010	28.02.2010

 

Many thanks, Christian

 

 

 

[NailDyanC]

Hi Christian,

What is the data type of that "Start Date" field in your first screen shot? If you would like to format time format you may go to the DataPage and configure the custom formatting of that field. You may check the screen shot below:

 

[Kurumi]

Hi @drckarcher - you may try to convert it to your desired format first using Excel and re-import it back to your account. Take note that in the table all dates will be in a US format and as mentioned above, you can customize in the DataPage level.

[drckarcher]

Yes, it seems easier to do all the converting and reformatting in excel. Many thanks!