Hi guys,
I am little bit confused, don’t know how to transform this type of scenario
we have 2 column in data session start and session end time
if we considered 1st row of data, Start time is 5:40:21 and end time is 06:01:56.
we are considering if user watched the TV from 1 AM to 6 PM it should go to 1st day part
if user watch TV after 6 AM to 9 AM it should go into 2nd day part and so on.
I want to check if user watch TV in 1st day part start time 5:40:21 the duration split into 2 rows
before 6 time (second) showing the record in 1st row.
Rest of the duration start with 6:01:56 means 116 second should show in next record.
there are few start time and end time start and end in a same day part.
I can handle this type of scenario but if user watched TV in 2 day part 1 and 2 or may by 1,2,3
how to split duration in different day part.
It all depends upon how your datetimes are stored. If it is the normal Excel way as numbers (see first 3 rows of image) then one can split into integer (days) and fractional parts (time). If it is as a text string extracted from a database then text splitting operations will work. In the later case further transformation is required before calculation (e.g. time elapsed) is possible.
