Need help with formulating time and date

[ranz_vincent]

Hello everyone I am trying to find a way to find how many deliveries are within a certain time window. Like i need a way to identify what truck arrived before 6am of the following day, after 6am but before 12nn, after 12nn but before 6pm and 6pm onwards. The starting date is the order_date.

[BA_Pete · Accepted solution]

Hi @ranz_vincent ,

Add a new custom column like this:

arrivalDayWindow =
if [date_arrived_to_store] = [order_date] then "Same Day"
else if [date_arrived_to_store] = Date.AddDays([order_date], 1) then "Next Day"
else "Other"

Then another one like this:

arrivalTimeWindow =
if [time_arrived_to_store] < #time(6, 0 ,0) then "Before 0600"
else if [time_arrived_to_store] >= #time(6, 0 ,0)
    and [time_arrived_to_store] < #time(12, 0 ,0) then "0600-1200"
else if [time_arrived_to_store] >= #time(12, 0 ,0)
    and [time_arrived_to_store] < #time(18, 0 ,0) then "1200-1800"
else "After 1800"

Between these two columns you should be able to make counts of any scenario you need.

Pete

[ranz_vincent]

Just finished trying this and it worked perfectly. Thanks for the help