Dates and times crossing into new days

[ElBB23]

Hi all, hoping I can get help with this one.

I have timing data that splits into segments with start date, start time and end time. The problem is this date can fall into the following day but the date doesn't change in the file.

Example below.

DATE START END
30/12/2024. 22:00. 23:50
30/12/2024. 23:50. 00:15
30/12/2024. 00:15. 03:00
30/12/2024. 03:00. 05:00

In the example above line 3 actually starts on the 31st so it needs to look like this.

DATE START END

30/12/2024. 22:00. 23:50
30/12/2024. 23:50. 00:15
31/12/2024. 00:15. 03:00
31/12/2024. 03:00. 05:00

Any help would be appreciated, I've been trying all day!

 

[Micron]

The problem must be in whatever you're doing that "splits into segments" so why not explain how that's done? Better to copy/paste cells from your wb rather than typing them out. Even better is to use XL2BB if you can (see posting toolbar).

 

[ElBB23]

The problem must be in whatever you're doing that "splits into segments" so why not explain how that's done? Better to copy/paste cells from your wb rather than typing them out. Even better is to use XL2BB if you can (see posting toolbar).

Hi,

Thanks for the reply, unfortunately it is not something I am doing with the data, this is how its received from an external source, hence why I am using power query to try and manipulate it into the right format.

 

[Micron]

Sorry, I failed to notice the forum you posted in. I don't use PQ so can't be of much help.
Good luck!

 

[AlienSx]

let
    Source = #table(
        {"DATE", "START", "END"},
        {
            {#date(2024, 12, 30), #time(22, 00, 0), #time(23, 50, 0)},
            {#date(2024, 12, 30), #time(23, 50, 0), #time(0, 15, 0)},
            {#date(2024, 12, 30), #time(0, 15, 0), #time(3, 00, 0)},
            {#date(2024, 12, 30), #time(3, 00, 0), #time(5, 00, 0)}
        }
    ), 
    lst = List.Buffer(Table.ToList(Source, (x) => x)),
    gen = List.Generate(
        () => [i = 0, r = lst{0}, res = r, add = Number.From(r{2} < r{1})],
        (x) => x[i] < List.Count(lst), 
        (x) => [i = x[i] + 1, r = lst{i}, res = {Date.AddDays(x[res]{0}, x[add])} & List.LastN(r, 2), add = Number.From(r{2} < r{1})], 
        (x) => x[res]
    ), 
    result = Table.FromList(gen, (x) => x, Table.ColumnNames(Source))
in
    result

 

[GraH]

DATE START END
30/12/2024. 22:00. 23:50
30/12/2024. 23:50. 00:15
30/12/2024. 00:15. 03:00
30/12/2024. 03:00. 05:00

In the example above line 3 actually starts on the 31st so it needs to look like this.

DATE START END

30/12/2024. 22:00. 23:50
30/12/2024. 23:50. 00:15
31/12/2024. 00:15. 03:00
31/12/2024. 03:00. 05:00

How do you know this? There must be something in your data that allows you to know when the day shifts?

And while the solution offered by @AlienSx is better in many ways, this is a UI build solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Set_types = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"START", type time}, {"END", type time}}),
    Add_helper = Table.AddColumn(Set_types, "Add Day", each if [START] > [END] then 1 else null),
    Fill_down = Table.FillDown(Add_helper,{"Add Day"}),
    Add_date_corrected = Table.AddColumn(Fill_down, "Date Corrected", each if [Add Day] = 1 and [START] < [END] then Date.AddDays([DATE],[Add Day]) else [DATE], type date),
    Remove_helper = Table.RemoveColumns(Add_date_corrected,{"Add Day", "DATE"})
in
    Remove_helper

Also, there might be a way the team behind this "external source extract" can solve the issue for you up front.

 

[GraH]

let
    Source = #table(
        {"DATE", "START", "END"},
        {
            {#date(2024, 12, 30), #time(22, 00, 0), #time(23, 50, 0)},
            {#date(2024, 12, 30), #time(23, 50, 0), #time(0, 15, 0)},
            {#date(2024, 12, 30), #time(0, 15, 0), #time(3, 00, 0)},
            {#date(2024, 12, 30), #time(3, 00, 0), #time(5, 00, 0)}
        }
    ),
    lst = List.Buffer(Table.ToList(Source, (x) => x)),
    gen = List.Generate(
        () => [i = 0, r = lst{0}, res = r, add = Number.From(r{2} < r{1})],
        (x) => x[i] < List.Count(lst),
        (x) => [i = x[i] + 1, r = lst{i}, res = {Date.AddDays(x[res]{0}, x[add])} & List.LastN(r, 2), add = Number.From(r{2} < r{1})],
        (x) => x[res]
    ),
    result = Table.FromList(gen, (x) => x, Table.ColumnNames(Source))
in
    result

I'm studying this as I wouldn't have thought going down the list alley (I hope it get's the solution mark from the @Micron ). I had sort of the same basic idea, but used the record approach:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Set_types = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"START", type time}, {"END", type time}}),
    Buffered = Table.Buffer(Set_types),
    CorrectRecord = List.Generate ( () => 
                    [
                        c = 0,
                        x = Table.RowCount(Buffered),
                        Rec = Buffered{c},
                        RecOut = [ 
                                    NewDate = Rec[DATE],
                                    START = Rec[START],
                                    END = Rec[END]
                                ],
                        Test = Rec[END] < Rec[START]
                    ],
                    each [x] > 0,
                    each 
                    [
                        x = [x] - 1,
                        c = [c] + 1, 
                        Rec = Buffered{c},
                        RecOut = [ 
                                    NewDate = if [Test] = true then Date.AddDays(Rec[DATE],1) else [RecOut][NewDate],
                                    START = Rec[START],
                                    END = Rec[END]
                                ],
                        Test = Rec[END] < Rec[START]
                    ]
                , each [RecOut]
                ),
    Restore_table = Table.FromRecords(CorrectRecord, type table [NewDate = Date.Type, START = Time.Type, END = Time.Type ])
in
    Restore_table

 

[Micron]

I hope it get's the solution mark from the @Micron

It's up to the original poster (OP) as to which post gets marked as the solution - subject to the purview of a moderator I guess.