Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 OffDayDelivery Time
Point A to Point B10:00116:00
Point A to Point C14:00209: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!!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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!

  • AlB's avatar
    AlB
    7 years ago

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

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you!

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

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