Jump to content
  • 0

DateDiff Calculation adding hour in error




I'm having difficulty with my time formula for a specific scenario. It is working correctly but producing too much time in some instances. 


I'm using a calculated field with the following formula to find the total time worked each day:

cast (Datediff(hour, [@field:Start_TIme], [@field:End_Time])%24 as varchar)+' hours,'+

cast (Datediff(minute, [@field:Start_Time], [@field:End_Time])%60 as varchar)+ ' minutes'


The formula is working when: 

The time calculated is more than an hour (i.e. 2:00 PM to 4:45 PM) = 2hrs, 45 minutes (CORRECT RESULT)


The time calculated is less than an hour but within the same hour of the day (i.e. 2:00 PM to 2:23 PM) = 0hrs, 23 minutes (CORRECT RESULT)


The formula is NOT working when: 

The time calculated is less than an hour across DIFFERENT hours (i.e. 2:50PM - 3:05PM) = 1hrs, 15 minutes (INCORRECT RESULT)

The hour should be '0' as the total time worked is '15' minutes only. 


I'm not sure if I can build a condition into the already existing formula(s) or if a new formula(s) is required. The standard datediff function also produces the same result.


I've tried other code located online but have not been successful at removing the hour that appears in error. I've searched the CASPIO forum but was not able to locate a like issue. 


Any help or suggestions would be much appreciated. 

Thank you!



Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

I was able to figure out the formula needed to count the datediff in hours and minutes.  The datediff calculation will always count the movement from one hour to the next as an 'Hour' in the calculated result in error.  For example, 12:50 PM to 1:10PM will show the calculated result as 1 hr 20 minutes. The result should be only 20 minutes. This is due to the transaction number being counted anytime a new hour begins. The formula to obtain the correct result is as follows: 

CONVERT(varchar(3),DATEDIFF(minute,Start_Time, End_Time)/60) + ':' +

          RIGHT('0' + CONVERT(varchar(2),DATEDIFF(minute,Start_Time, End_Time)%60),2)


The formula will present the time calculation formatted with a colon (ie., 0:20)

The bolded text should be the name of the field that you are using in your application. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...