Time calculation

[Anonymous]

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!!

[Anonymous · Accepted solution]

@Anonymous 

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!

[Anonymous · Accepted solution]

@Anonymous 

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!

[Anonymous]

Thank you!

[AlB · Accepted solution]

Hi @Anonymous 

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] )