Function to extract column data

[Babycakes]

Hi There,

We  have a data set that I am trying to create a formula for;

Trying to define this one shift but each shift is different buy basic principle applies.

Shift starts Monday week 1 at 3 pm and 3 pm to 10 pm is paid afternoon rate

Same shift is split so two lines in data set.

Second component is hence on Week 1 Tuesday 6 am to 9am

How do i create a formula that identifies this eg changes to 3 pm to 10 pm when it is two seperate lines.

I have attached the sheet

Current

Shift Start	Sleepover Start	Sleepover Finish	Allocated Shift Break (mins)	Shift Finish	Worked Hours in Day	Shift Splits	Total shift hours	Week	Day
3:00 PM	10:00 PM	6:00 AM	0	9:00 AM	7.00	420, 480, 180	10	1	Monday
3:00 PM	10:00 PM	6:00 AM	0	9:00 AM	3.00	420, 480, 180	10	1	Tuesday

[Anonymous · Accepted solution]

Hi @Babycakes 

You can refer to the following solution.

1.Create a Index column

Index = var a=ADDCOLUMNS('Table',"DayNum",SWITCH([Day],"Monday",1,"Tuesday",2,"Wednesday",3,"Thursday",4,"Friday",5,"Saturday",6,"Sunday",7))
return RANKX(FILTER(a,[Shift Start]=EARLIER('Table'[Shift Start])),[Day],,ASC)

2.Then create the following columns

Sub_Shift Start = IF([Index]=1,[Shift Start],[Sleepover Finish])

Sub_Shift Finish = IF([Index]=1,[Sleepover Start],[Shift Finish])

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Babycakes]

Result

Shift Start	Sleepover Start	Sleepover Finish	Allocated Shift Break (mins)	Shift Finish	Worked Hours in Day	Shift Splits	Total shift hours	Week	Day
3:00 PM	10:00 PM	6:00 AM	0	10:00 PM	7.00	420, 480, 180	10	1	Monday
6:00 AM	10:00 PM	6:00 AM	0	9:00 AM	3.00	420, 480, 180	10	1	Tuesday