# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Creator II

## Difference between 2 dates in Hours

Hi,

I have 2 date fields startdate,enddate with timestamps.

Now i want to find out the difference between those 2 dates in terms of number of hours.

How to get this ?

4 Replies
MVP

Try this way

interval( enddate - startdate , 'HH' )

Regards

Anand

Partner - Specialist II

May be like this ...

Hour(startdate) - Hour(enddate) as Diff_Hours

I hope this might helps you.

-Nagarjun

Creator III

Hi Mohan Krishna,

//All calculations are based on the decimal representation of date and time

Set vStartTime = .375; //9:00am

Set vCloseTime = .708333333334; // 5:00PM

Set vFullDay = .333333334; // 8 hours - full day

FirstPass:

StartDateTime,

// calculate total hours worked excluding the Start Day and the Close Day

NetWorkDays(StartDateTime+1,CloseDateTime-1) * 8 as HoursWorked,

//calculates the decimal number for hours after the Start time up to a maximum full days work (9:00am to 5:00pm)

IF(num#(Time#(Frac(StartDateTime),'hh:mm'))>num#(\$(vStartTime)) and  num#(Time#(Frac(StartDateTime),'hh:mm'))>num#(\$(vCloseTime)) ,0,if(num#(Time#(Frac(StartDateTime),'hh:mm'))>num#(\$(vStartTime)),num#(\$(vCloseTime))-num#(Time#(Frac(StartDateTime),'hh:mm')),num#(\$(vFullDay))))as StartDayTime,

//calculates the decimal number for hours before the Close time up to a maximum full days work (9:00am to 5:00pm)

IF(num#(Time#(Frac(CloseDateTime),'hh:mm'))>num#(\$(vStartTime)) and  num#(Time#(Frac(CloseDateTime),'hh:mm'))>num#(\$(vCloseTime)) ,num#(\$(vFullDay)),if(num#(Time#(Frac(CloseDateTime),'hh:mm'))>num#(\$(vStartTime)),-num#(\$(vStartTime))+num#(Time#(Frac(CloseDateTime),'hh:mm')),0))as CloseDayTime

FROM

(biff, embedded labels, table is Sheet2\$);

New:

StartDateTime,

CloseDateTime,

HoursWorked,

//We now have 'HoursWorked' which excludes the Start Day and Close Day

//the script below calculates the hrs and mins for those two days

//The first part calculates hrs and min when the start date and close date are the same day

//If the day is Sat or Sun then 0 hours

if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0 and num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,

//test if the Start date and Close date are the same

if(Date(floor(StartDateTime)) = Date(floor(CloseDateTime)),

// test to see if the Start time is before 9:00am

if(StartDayTime  = num#(\$(vFullDay)),

//get the hours and minutes up to the close time

Floor(CloseDayTime * 24) & ' Hrs ' & Floor(Frac(CloseDayTime * 24)* 60) & ' Min',

// test to see if the Close time is after 5:00pm

if(CloseDayTime  = num#(\$(vFullDay)),

//get the hours and minutes for the Start day time

Floor(StartDayTime * 24) & ' Hrs ' & Floor(Frac(StartDayTime * 24)* 60) & ' Min',

//If the start time is after 8:00am and close time if before 5:00pm calcuate the hours and minutes

Floor(StartDayTime * 24) + Floor(CloseDayTime * 24)-8 + (Floor((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)) & ' Hrs ' & Frac((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)*60  & ' Min')),

//now calculate hrs and mins when the start date and close date are different days

//if the Start date is Sat or Sun then 0 hrs

if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0,0,

//get the Start time

Floor(StartDayTime * 24))

//add to the start time the close time

//if the close date is Sat or Sun then hours are zero (0)

+if(num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,

//calculate the close time hours taking into account the adding start minutes and end minutes can add up to 1 hour

Floor(CloseDayTime * 24)) + HoursWorked+ (Floor((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)) & ' Hrs  ' & Frac((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)*60  & ' Min'))

as FinalHours,

// below are some mid calculations that needed to be incorporated in the final answer above

NetWorkDays(StartDateTime,StartDateTime) as StartSatSun,

if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0,0,Floor(StartDayTime * 24))as SHours, // 0 if the date is a holiday

if(num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,Floor(CloseDayTime * 24))as CHours, // 0 if the date is a holiday

if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0,0,Floor(StartDayTime * 24))+if(num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,Floor(CloseDayTime * 24)) + HoursWorked as SCHours,

(Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60  as SCMinutes,

Floor((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)  as HrsIfMinutesSandCTotalOver1Hour,

Frac((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)*60   as remMinute,

if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0,0,Floor(StartDayTime * 24))+if(num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,Floor(CloseDayTime * 24)) + HoursWorked+ (Floor((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)) as SCHoursFinal,

NetWorkDays(CloseDateTime,CloseDateTime) as CloseSatSun,

Floor(CloseDayTime * 24)  as CloseHours,

Floor(Frac(CloseDayTime * 24)* 60)  as CloseMinutes,

Frac(CloseDayTime * 24*60 )*60  as CloseSecconds,

(StartDayTime * 24)  as StartFirst,

Floor(StartDayTime * 24)  as StartHours,

Floor(Frac(StartDayTime * 24)* 60)  as StartMinutes,

Frac(StartDayTime * 24*60 )*60  as StartSecconds

Resident FirstPass;

Regards

Hemanth

Creator II

use the below query to find the duration time between the two dates

=time((Time('End date','HH:MM' define format))

-

(Time('Start Date','HH:MM'  define format ) ),'HH')

hope this helps

Community Browser