Search the Community

Hello. I have a timesheet report representing hours worked. Relevant field names are: StartTime, EndTime and TimeSpan (a calculated field - let Caspio sweat the routine calculations). I'm struggling with the syntax in the "Aggregate" function entry. My goal is an output which shows: HH:MM... For example, if my time entries are: StartTime EndTime TimeSpan 08:30 10:30 2:00 09:45 10:45 1:00 13:30 15:45 2:15 (sum = 4:15) If I use: sum(datediff(minutes, StartTime, EndTime )) I get 255... (4 * 60 + 15) But how in the world do I present the output as 4:15 and not 255. Yes, I've changed the 'Formatting' to Time (HH:MM), to no avail. Any help would be deeply appreciated!

Hello Community, I´m new to Caspio and my try, to use a calculated field in a table ends with an error (see Screenhot here: http://prntscr.com/cxgbw8) in the formula: Invalid formular: Incorrect syntax near PERSISTED I´ve used this formular DateDiff(year, [@field:P_DOB], GetUTCDate() as it is shown in the help section (http://howto.caspio.com/faq/reports-datapages/calculated-fields-and-datediff-function/). Any ideas about my wrong usage? Thanks for your comments!

I have a table which includes a timestamp and a type field (among others) I've created triggers and a view that combines this table into itself and displays a clock-in time, clock-out time, and a total time difference between them in HH:MM format. The formula in the calculated field is below: CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]), 0), 114) My issue is that I need to have a total/aggregate for this field, but it gives an invalid error if I just do sum, and I've been unable to figure out how to get it to add the time and display it in the HH:MM format. Any help would be great!

Hello, I have a number field ('Qty') in a submission form that is for recording the quantity of hours worked. So for example if someone worked 1 hour and 15 minutes the Qty value would be entered as 1.25. A new option I put in the submission form is a Start_Time field and an End_Time field (both Date/Time fields). A button running js puts the current date/time stamp in the Start_Time field and then when the user is done they click a button to put the new current date/time stamp in the End_Time field. The difference between the two are the minutes worked, put in the Qty field via CalculatedField setting: Datediff(minute,[@field:Start_Time],[@field:End_Time])/60.00. This seems to work well so far. What I then need to do is Round Up to the nearest 15 minute value. For example, if the calculation for Start_Time 12/31/2019 11:45 and End_Time 12/31/2019 14:10 returns 2.416666 I need it to round up to 2.5. I've tried various Round() scenarios but can't get it right. Basically, I want the Qty value to always be a version of .25, .5, .75, 1, 1.25, 1.5, 1.75, 2, etc. Does anyone know how to use Round in the calculation above to do that? Many thanks!

In excel if I wanted to know the number of Year and months and days until something I would use: =DATEDIF(B2,TODAY(),"y") & " Years, " & DATEDIF(B2,TODAY(),"ym") & " Months, " & DATEDIF(B2,TODAY(),"md") & " Days" In CB calculated fields I can get one of the intervals - year OR month OR day but not all three using: Datediff(day,GetUTCDate(),[@field:ExpirationDate]) Can I convert that excel datediff to a CB friendly alrternative?

Hello, 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) or 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!