cancel
Showing results for
Did you mean:
Helper II

## Time calculation

Hi,

Another query on calculating dates/times, but I haven't seen anything similar to what I'm trying to do, so I'd appreciate any help!

When we place an order with a carrier, we speicify the delivery date/time that we want. We have cut off times agreed with our carriers for when we will place the order for them to then deliver by the requested date, and I need to see how often we are failing to hit that cut off.

The data is set by times in one column and day number in another; today is Day 0, tomorrow is Day 1, next day is Day 2 etc

 Order Cut Off Day Delivery Time Point A to Point B 10:00 1 16:00 Point A to Point C 14:00 2 09:00

In this example, if we want an item delivering to Point B tomorrow, we would need to book the order by 10:00 today.  Point C is further away - we need to book before 14:00 Day 0 to ensure delivery at 09:00 Day 2.

Essentially, Point B requires 30 hours lead time, point C needs 43 hours. How do I calculate that from this date in a new column?

Many thanks!!

2 ACCEPTED SOLUTIONS
Resolver IV

Thanks for detailed explaination

Use below formula in Calculated Column

Spoiler
```Duration (Hours) =
Var StartDate = TODAY()+'Table'[Order Cut Off]
Var EndDate = TODAY()+'Table'[Delivery Time] + 'Table'[Day]
RETURN
(EndDate - StartDate) * 24```

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Proud to be Datanaut!

Super User

If Delivery Time and Order Cut Off are formatted as Time and Day as whole number, you can simply do

`NewColumn = 24*(Table1[Delivery Time] + Table1[Day] - Table1[Order Cut Off] )`

3 REPLIES 3
Resolver IV

Thanks for detailed explaination

Use below formula in Calculated Column

Spoiler
```Duration (Hours) =
Var StartDate = TODAY()+'Table'[Order Cut Off]
Var EndDate = TODAY()+'Table'[Delivery Time] + 'Table'[Day]
RETURN
(EndDate - StartDate) * 24```

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Proud to be Datanaut!

Helper II

Thank you!

Super User

If Delivery Time and Order Cut Off are formatted as Time and Day as whole number, you can simply do

`NewColumn = 24*(Table1[Delivery Time] + Table1[Day] - Table1[Order Cut Off] )`

Announcements

#### Launching new user group features

Learn how to create your own user groups today!