Search the Community
Showing results for tags 'datediff'.
Found 4 results
roattw posted a question in Calculations and aggregationsIn 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 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, 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!
SGProject posted a question in Calculations and aggregationsHello 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!