Time / Days Calculation Expression

[khanashique]

Hi there,

How do i calculate durations in days,hours,mins (need to use time / date expression)

Example: Start Date and Time = 01 Jan 2018, 07:30 AM

               End Date and Time = 02 Jan 2018, 10:00 PM

Difference comes: 1 Day, 14 Hours, 30 Mins.

I have used below expression:

TIMESTAMP([ENDDATE]-[STARTDATE],'HH:MM:SS') --> it shows calculation only in Time i want to see days too.

Thanks.

MAK

[MarcoWedel]

Interval([ENDDATE]-[STARTDATE],'d hh:mm:ss')

[khanashique]

Thanks for the reply, but the above expression gives difference only in days, no time is being calculated

[MarcoWedel]

The expression seems to work for me, i.e it gives difference not only in days, but time is being calculated also:

hope this helps

regards

Marco

[khanashique]

Thanks once again for your effort...

but i get same calculation even after i normally subtract end date and start date by changing the pattern in properties 'DD HH:MM:SS'

I need to see somthing like below mentioned

" 1 Day, 14 Hours, 30 Mins."

Thanks.

MAK.

[MarcoWedel · Accepted solution]

maybe like this?

table1:
LOAD *,
    Dual(' '&Floor([ENDDATE]-[STARTDATE])&' Day, '&Hour([ENDDATE]-[STARTDATE])&' Hours, '&Minute([ENDDATE]-[STARTDATE])&' Mins.',[ENDDATE]-[STARTDATE]) as INTERVAL
INLINE [
    STARTDATE, ENDDATE
    "01 Jan 2018, 07:00 AM", "02 Jan 2018, 07:00 AM"
    "01 Jan 2018, 07:30 AM", "02 Jan 2018, 10:00 PM"
    "02 Jan 2018, 12:00 AM", "02 Jan 2018, 12:00 PM"
    "03 Jan 2018, 01:00 PM", "07 Jan 2018, 02:34 PM"
];

regards

Marco

[khanashique]

Hi Marco,

This is great, thanks a lot, it worked...

Can i ask little more help....

Example:

Working Day --> Monday to Friday

Week Off Day --> Saturday and Sunday

Shifts Hours --> 07:30 AM to 05:30 PM

So the time calculation should exclude the days and hours other than working day and shift hours.

Thanks,

MAK.