Day start function on time and date field

[#1]

Hi All;

I have the below formula to calculate my LoadingDuration. The problem is its not retaining the correct calculations.

Table2:

    Load* ,

Interval(if(len([First Pick Departure Date])>=4 and Hour([First Pick Departure Date])>=3 or Hour([First Pick Departure Date])<8,DayStart([First Pick Departure Date],1,1/3 ),[First Pick Departure Date])-if(len([First Pick Arrival Date])>=4 and Hour([First Pick Arrival Date])>=3 or Hour([First Pick Arrival Date])<8 ,DayStart([First Pick Arrival Date],1,1/3),[First Pick Arrival Date])) as LoadingDuration;

Load Timestamp#([First Pick Departure Date], 'DD/MM/YYYY hh:mm') as [First Pick Departure Date],

Timestamp#([First Pick Plan Date Start], 'DD/MM/YYYY hh:mm') as [First Pick Plan Date Start],

Timestamp#([First Pick Plan Date End], 'DD/MM/YYYY hh:mm') as [First Pick Plan Date End],

  [Lean ID],

Timestamp#([First Pick Arrival Date], 'DD/MM/YYYY hh:mm') as [First Pick Arrival Date]

FROM

[$(vDataPath)\DASHBOARD REPORT- Randfontein.xlsx]

(ooxml, embedded labels, table is Sheet1);

The following incorrect values are being retained:

First Pick Arrival Date	First Pick Departure Date	LoadingDuration
01/12/2015 09:57	01/12/2015 14:45	0:00:00
01/12/2015 21:38	02/12/2015 02:05	0:00:00
02/12/2015 03:51	02/12/2015 07:41	0:00:00
02/12/2015 07:30	02/12/2015 10:20	24:00:00
02/12/2015 07:30	02/12/2015 10:30	24:00:00

What might be the problem?

Regards;

Moses

[marcus_sommer · Accepted solution]

I think your conditions couldn't be correct. I see no reason for a len-check and the Hour-checks should be done per AND or both parts needs to be wrapped in brackets. Here an suggestion how it looked more reasonable (Hours-check is replaced with match() which is shorter in this case):

Load *,

Interval(

    if(match(Hour([First Pick Departure Date]), 3,4,5,6,7),

          DayStart([First Pick Departure Date],1,1/3 ),[First Pick Departure Date]) -

    if(match(Hour([First Pick Arrival Date]), 3,4,5,6,7),

          DayStart([First Pick Arrival Date],1,1/3),[First Pick Arrival Date])) as LoadingDuration;

....

- Marcus

[swuehl]

Would be helpful if you could describe your requirement for LoadingDuration calculation (AKA so we get to know why the calculated LoadingDuration values are 'incorrect').

The if-condition in your LOAD script will always return TRUE, so always the THEN branches (Daystart() functions) will be executed.

This seems incorrect to me (why do we need an if-statement then?), but without knowing your requirements, I can't tell what to change.

The Daystart()-functions seem to work for me, but again, without knowing what you are trying to achieve.

First Pick Arrival Date	DaystartFPAD	First Pick Departure Date	DaystartFPDD	LoadingDuration
01/12/2015 09:57	02.12.2015 08:00:00	01/12/2015 14:45	02.12.2015 08:00:00	0
01/12/2015 21:38	02.12.2015 08:00:00	02/12/2015 02:05	02.12.2015 08:00:00	0
02/12/2015 03:51	02.12.2015 08:00:00	02/12/2015 07:41	02.12.2015 08:00:00	0
02/12/2015 07:30	02.12.2015 08:00:00	02/12/2015 10:20	03.12.2015 08:00:00	1
02/12/2015 07:30	02.12.2015 08:00:00	02/12/2015 10:30	03.12.2015 08:00:00	1

[#4]

Thanks Marcus, it worked!