Difference between 2 dates in Hours

mohan2391 — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

Re: Difference between 2 dates in Hours

its_anandrjs — Wed, 09 Nov 2016 10:57:31 GMT

Try this way

interval( enddate - startdate , 'HH' )

Regards

Anand

Re: Difference between 2 dates in Hours

nagarjuna_kotha — Wed, 09 Nov 2016 11:04:04 GMT

May be like this ...

Hour(startdate) - Hour(enddate) as Diff_Hours

I hope this might helps you.

-Nagarjun

Re: Difference between 2 dates in Hours

hemanthaanichet — Wed, 09 Nov 2016 11:27:25 GMT

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

Re: Difference between 2 dates in Hours

eromiyasimon — Wed, 09 Nov 2016 11:33:39 GMT

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

topic Re: Difference between 2 dates in Hours in QlikView

Difference between 2 dates in Hours

Re: Difference between 2 dates in Hours

Re: Difference between 2 dates in Hours

Re: Difference between 2 dates in Hours

Re: Difference between 2 dates in Hours