Skip to main content

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 
Search instead for 
Did you mean: 
mohan2391
Creator II
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
its_anandrjs

Try this way

interval( enddate - startdate , 'HH' )

Regards

Anand

nagarjuna_kotha
Partner - Specialist II
Partner - Specialist II

May be like this ...

Hour(startdate) - Hour(enddate) as Diff_Hours



I hope this might helps you.



-Nagarjun

hemanthaanichet
Creator III
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:

LOAD CloseDateTime,

     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

C:\Users\Jarvis\Downloads\NetHoursStartDateCloseDateandTime.xls

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

New:

load

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

eromiyasimon
Creator II
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