Figuring out date based on time only

[mjacquot82]

Good day everyone,

I am trying to figure out the following. I have the date and time of delivery, i have the time that loading is starting. What i don't have is the date of loading. Loading is obviously always before loading. So when i look at the schedule i can easily figure out by looking my schedule the date of loading. But i need to get the spreadsheet do it for me. To throw a wrench in things, depending on where it is delivering it will affect the equation.

example : Warehouse 1 - Monday delivery ( is the date in the sheet) loading time 10 pm, delivery is Monday the 28th @ 4 am. So myself i know looking at it that loading is the 27th.
Warehouse 2 - Monday delivery (is the date in the sheet) loading time 5 pm, delivery is Monday the 28th @ 9:20 pm. So myself i know that even thought is says delivery is Monday the 28th the first delivery will in fact be Sunday the 27th)

Sheets has Monday Date : November 28th 2022 as the delivery day

Warehouse 1 receiving starts @ 4:00 am ( on this sheet it tells me the receiving date, there is no loading date )
Warehouse 2 receiving starts @ 9:20 pm the day before ( again same sheet)

The only info i have is the delivery date and delivery times but no loading date.

I have set up automatic emails with the delivery information ( date and time ) but what i can't have automatically entered is the loading date as it varies so much.

 

[jdellasala]

Dates with Time and Duration are very tricky to handle with Excel formulas (at least for me), so I tried Power Query instead which handles it a LITTLE better. This is what I ended up with:

Book1
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3		Warehouse	# of Trucks	Loaded	Delivered	Hours to Deliver
2	Delivery Date	11/28/2022			W1	1	11/28/2022 21:00	11/29/2022 04:00	0.07:00:00
3					W1	2	11/28/2022 23:00	11/29/2022 05:30	0.06:30:00
4	W1				W1	1	11/28/2022 01:00	11/28/2022 07:00	0.06:00:00
5	Load Time	# of Trucks	Delivery Time		W2	2	11/28/2022 15:30	11/28/2022 21:20	0.05:50:00
6	09:00 PM	1	04:00 AM		W2	2	11/28/2022 17:00	11/28/2022 23:20	0.06:20:00
7	11:00 PM	2	05:30 AM		W2	1	11/28/2022 19:00	11/29/2022 01:20	0.06:20:00
8	01:00 AM	1	07:00 AM
9					Warehouse	Total # of Trucks	Total Hours to Deliver
10	W2				W1	4	0.19:30:00
11	Load Time	# of Trucks	Delivery Time		W2	5	0.18:30:00
12	03:30 PM	2	09:20 PM
13	05:00 PM	2	11:20 PM
14	07:00 PM	1	01:20 AM
Sheet2

(At least it wasn't a lot of data, but would have been easier if you had used XL2BB)
Now this is no masterpiece, but it does get the job done, but does have a flaw. The Warehouse Names (W1 and W2) are hard coded into the query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    KeepFirstRow = Table.FirstN(Source,1),
    KeepColumnTwo = Table.SelectColumns(KeepFirstRow,{"Column2"}),
    ChangedToDate = Table.TransformColumnTypes(KeepColumnTwo,{{"Column2", type date}}),
    DeliveryDate = ChangedToDate{0}[Column2],
    Custom1 = Source,
    RemovedTopTwoRows = Table.Skip(Custom1,2),
    AddedWarehouse = Table.AddColumn(RemovedTopTwoRows, "Custom", each if [Column1]="W1" then "W1" else 
if [Column1]="W2" then "W2" else null),
    FilledDown = Table.FillDown(AddedWarehouse,{"Custom"}),
    RemovedTopRow = Table.Skip(FilledDown,1),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRow, [PromoteAllScalars=true]),
    RenamedWarehouseColumn = Table.RenameColumns(PromotedHeaders,{{"W1", "Warehouse"}}),
    ChangedTrucksType = Table.TransformColumnTypes(RenamedWarehouseColumn,{{"# of Trucks", Int64.Type}}),
    ReplacedTrucksErrors = Table.ReplaceErrorValues(ChangedTrucksType, {{"# of Trucks", null}}),
    FilteredNullRows = Table.SelectRows(ReplacedTrucksErrors, each ([#"# of Trucks"] <> null)),
    ChangedType1 = Table.TransformColumnTypes(FilteredNullRows,{{"Load Time", type time}, {"Delivery Time", type time}, {"Warehouse", type text}}),
    AddedLoaded = Table.AddColumn(ChangedType1, "Loaded", each #datetime( Date.Year( DeliveryDate ), Date.Month(DeliveryDate), Date.Day(DeliveryDate), Time.Hour([Load Time]), Time.Minute([Load Time]), 0 )),
    ChangedLoadedType = Table.TransformColumnTypes(AddedLoaded,{{"Loaded", type datetime}}),
    AddedDelivered = Table.AddColumn(ChangedLoadedType, "Delivered", each 
        if [Delivery Time] < [Load Time] then 
            #datetime( Date.Year(DeliveryDate), Date.Month(DeliveryDate), Date.Day(DeliveryDate)+1, 
                Time.Hour([Delivery Time]), Time.Minute([Delivery Time]), 0 ) 
        else  #datetime( Date.Year(DeliveryDate), Date.Month(DeliveryDate), Date.Day(DeliveryDate), 
                Time.Hour([Delivery Time]), Time.Minute([Delivery Time]), 0)),
    ChangedDeliveredType = Table.TransformColumnTypes(AddedDelivered,{{"Delivered", type datetime}}),
    InsertedHoursToDeliver = Table.AddColumn(ChangedDeliveredType, "Hours to Deliver", each [Delivered] - [Loaded], type duration),
    ReorderedColumns = Table.ReorderColumns(InsertedHoursToDeliver,{"Warehouse", "# of Trucks", "Loaded", "Delivered", "Hours to Deliver", "Load Time", "Delivery Time"}),
    RemovedColumns = Table.RemoveColumns(ReorderedColumns,{"Load Time", "Delivery Time"}),
    SortedRows = Table.Sort(RemovedColumns,{{"Warehouse", Order.Ascending}})
in
    SortedRows

The entire range was loaded at first and parsed to determine the DeliveryDate (step 5) after which the Source is brought back to get the report where DeliveryDate is used as a variable.
Note that the AddedWarehouse step hard codes the 2 Warehouse names. This Query is the top Green table - query named DeliveryReport. Its output is Referenced in the second green query named DeliverySummary which has only one step to group the Report by Warehouse with the Sum of the Trucks and Hours columns.

let
    Source = DeliveryReport,
    GroupedByWarehouse = Table.Group(Source, {"Warehouse"}, {{"Total # of Trucks", each List.Sum([#"# of Trucks"]), type nullable number}, {"Total Hours to Deliver", each List.Sum([Hours to Deliver]), type duration}})
in
    GroupedByWarehouse

One final note. This will NOT work if the delivery takes more than 24 hours, but I assume since the initial data doesn't provide a date, that "never" happens! The Start and End times should really have Dates from the get go.
I did start with trying to work out formulas, but got to this first. I'll be interested in any formula solutions are posted.

 

[mjacquot82]

Dates with Time and Duration are very tricky to handle with Excel formulas (at least for me), so I tried Power Query instead which handles it a LITTLE better. This is what I ended up with:

Book1
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3		Warehouse	# of Trucks	Loaded	Delivered	Hours to Deliver
2	Delivery Date	11/28/2022			W1	1	11/28/2022 21:00	11/29/2022 04:00	0.07:00:00
3					W1	2	11/28/2022 23:00	11/29/2022 05:30	0.06:30:00
4	W1				W1	1	11/28/2022 01:00	11/28/2022 07:00	0.06:00:00
5	Load Time	# of Trucks	Delivery Time		W2	2	11/28/2022 15:30	11/28/2022 21:20	0.05:50:00
6	09:00 PM	1	04:00 AM		W2	2	11/28/2022 17:00	11/28/2022 23:20	0.06:20:00
7	11:00 PM	2	05:30 AM		W2	1	11/28/2022 19:00	11/29/2022 01:20	0.06:20:00
8	01:00 AM	1	07:00 AM
9					Warehouse	Total # of Trucks	Total Hours to Deliver
10	W2				W1	4	0.19:30:00
11	Load Time	# of Trucks	Delivery Time		W2	5	0.18:30:00
12	03:30 PM	2	09:20 PM
13	05:00 PM	2	11:20 PM
14	07:00 PM	1	01:20 AM
Sheet2

(At least it wasn't a lot of data, but would have been easier if you had used XL2BB)
Now this is no masterpiece, but it does get the job done, but does have a flaw. The Warehouse Names (W1 and W2) are hard coded into the query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    KeepFirstRow = Table.FirstN(Source,1),
    KeepColumnTwo = Table.SelectColumns(KeepFirstRow,{"Column2"}),
    ChangedToDate = Table.TransformColumnTypes(KeepColumnTwo,{{"Column2", type date}}),
    DeliveryDate = ChangedToDate{0}[Column2],
    Custom1 = Source,
    RemovedTopTwoRows = Table.Skip(Custom1,2),
    AddedWarehouse = Table.AddColumn(RemovedTopTwoRows, "Custom", each if [Column1]="W1" then "W1" else
if [Column1]="W2" then "W2" else null),
    FilledDown = Table.FillDown(AddedWarehouse,{"Custom"}),
    RemovedTopRow = Table.Skip(FilledDown,1),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRow, [PromoteAllScalars=true]),
    RenamedWarehouseColumn = Table.RenameColumns(PromotedHeaders,{{"W1", "Warehouse"}}),
    ChangedTrucksType = Table.TransformColumnTypes(RenamedWarehouseColumn,{{"# of Trucks", Int64.Type}}),
    ReplacedTrucksErrors = Table.ReplaceErrorValues(ChangedTrucksType, {{"# of Trucks", null}}),
    FilteredNullRows = Table.SelectRows(ReplacedTrucksErrors, each ([#"# of Trucks"] <> null)),
    ChangedType1 = Table.TransformColumnTypes(FilteredNullRows,{{"Load Time", type time}, {"Delivery Time", type time}, {"Warehouse", type text}}),
    AddedLoaded = Table.AddColumn(ChangedType1, "Loaded", each #datetime( Date.Year( DeliveryDate ), Date.Month(DeliveryDate), Date.Day(DeliveryDate), Time.Hour([Load Time]), Time.Minute([Load Time]), 0 )),
    ChangedLoadedType = Table.TransformColumnTypes(AddedLoaded,{{"Loaded", type datetime}}),
    AddedDelivered = Table.AddColumn(ChangedLoadedType, "Delivered", each
        if [Delivery Time] < [Load Time] then
            #datetime( Date.Year(DeliveryDate), Date.Month(DeliveryDate), Date.Day(DeliveryDate)+1,
                Time.Hour([Delivery Time]), Time.Minute([Delivery Time]), 0 )
        else  #datetime( Date.Year(DeliveryDate), Date.Month(DeliveryDate), Date.Day(DeliveryDate),
                Time.Hour([Delivery Time]), Time.Minute([Delivery Time]), 0)),
    ChangedDeliveredType = Table.TransformColumnTypes(AddedDelivered,{{"Delivered", type datetime}}),
    InsertedHoursToDeliver = Table.AddColumn(ChangedDeliveredType, "Hours to Deliver", each [Delivered] - [Loaded], type duration),
    ReorderedColumns = Table.ReorderColumns(InsertedHoursToDeliver,{"Warehouse", "# of Trucks", "Loaded", "Delivered", "Hours to Deliver", "Load Time", "Delivery Time"}),
    RemovedColumns = Table.RemoveColumns(ReorderedColumns,{"Load Time", "Delivery Time"}),
    SortedRows = Table.Sort(RemovedColumns,{{"Warehouse", Order.Ascending}})
in
    SortedRows

The entire range was loaded at first and parsed to determine the DeliveryDate (step 5) after which the Source is brought back to get the report where DeliveryDate is used as a variable.
Note that the AddedWarehouse step hard codes the 2 Warehouse names. This Query is the top Green table - query named DeliveryReport. Its output is Referenced in the second green query named DeliverySummary which has only one step to group the Report by Warehouse with the Sum of the Trucks and Hours columns.

let
    Source = DeliveryReport,
    GroupedByWarehouse = Table.Group(Source, {"Warehouse"}, {{"Total # of Trucks", each List.Sum([#"# of Trucks"]), type nullable number}, {"Total Hours to Deliver", each List.Sum([Hours to Deliver]), type duration}})
in
    GroupedByWarehouse

One final note. This will NOT work if the delivery takes more than 24 hours, but I assume since the initial data doesn't provide a date, that "never" happens! The Start and End times should really have Dates from the get go.
I did start with trying to work out formulas, but got to this first. I'll be interested in any formula solutions are posted.

wow that is a lot of code ! definately would not have been able to come up with something like this, Thank you ! It is in 2 sections here, how would i go about applying this ?

 

[jdellasala]

I don't think I actually wrote any of it - it's generated almost all by the UI which is easy to learn. Other than removing spaces from the Step names to make the code easier to read, I hardly did much.
As for how to do it, it's a bit more than can be done here, but there are some great Playlists on Power Query here and here. It won't take many to get what you need to know. The second link is a full college course on the subject with before and after sample files which I highly recommend!

 

[mjacquot82]

Geez there are lot of video's there, what did you use ? or what should i be looking to learn to achieve what you did ?

 

[jdellasala]

Geez there are lot of video's there, what did you use ? or what should i be looking to learn to achieve what you did ?

Go through the first few. That should get you up to speed. Sorry if I didn't mention that.