Forum Discussion
Function to extract column data
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 |
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.
2 Replies
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.
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
