How to calculate a Period with 2 Fields ?

[#1]

Hello everyone,

I have a table with 4 Columns :

- Shipment Date

- Shipment Time

- Arrival Date

- Arrival Time

Both of my Date columns contain data in such format : MM/DD/YYYY

As for my time Column, the data is represented as : HH:MM

What I am trying to do is to create a metric that would take into account both time and Date for Arrival and Shipment so that it gives back a Shipping Time Value for each entry of my table. If anyone can help I'd be really graceful.
Thanks a lot
Alexandre

[sunny_talwar]

So are you trying to find difference between Arrival Date & Time and Shipment Date & Time. If yes, then may be this:

Interval(TimeStamp#([Arrival Date] & ' ' & [Arrival Time], 'MM/DD/YYYY hh:mm') - TimeStamp#([Shipment Date] & ' ' & [Shipment Time], 'MM/DD/YYYY hh:mm'), 'D hh:mm') as Difference

[#3]

Hi,

I guess you could try to create a Timestamp in the script with date time (MM/DD/YYYY HH:MM) and then calculate the direct diference between the two Timestamps. Timestamp1 - Timestamp2.

Because Timestamps are very memory consuming, I would recommend that you do this in a auxiliary table and then join the results into your original table, and in the end drop the auxiliary table.

Hope It was useful...

Best Regards,

Bruno Silva.

Ps- If you need just to count the working hours, it is a little more tricky than this...

[maxgro]

Interval(

     (Date#( [Arrival Date], 'MM/DD/YYYY') + Time#([Arrival Time], 'hh:mm'))

     -

     (Date#( [Shipment Date], 'MM/DD/YYYY') + Time#([Shipment Time], 'hh:mm'))

     )

[sasiparupudi1]

Please post some sample data

[#6]

Hello Everyone,

first of all I'd like to thank all the persons that have replied to my question.
I have tried the interval formula that you people provided but the metrics then comes into a weird format (A single number).
What I am looking for is that after measuring the difference between both shipment and arrival, it gives me the result in hours (if it is possible ?).
Again I thank you all for the answers you took the time to write in order to help me. I will recommend this community to anyone

Have a nice day all of you !

[#7]

I wanted to join my Excel table but I havent managed to do that..
Would you know how to do it ?
Cheers And thank you for answering

[jonathandienst]

Have a look at this blog posting for ways of handling and analysing multiple date fields:

Canonical Date

[sasiparupudi1]

[sasiparupudi1]

Please post your excel file