cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Need help with formulating time and date

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.

1 ACCEPTED SOLUTION
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

2 REPLIES 2
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Frequent Visitor

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