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.

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.

## Question

## Breee

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 workingwhen: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(i.e. 2:00 PM to 2:23 PM) = 0hrs, 23 minutes (CORRECT RESULT)but within the same hour of the dayThe formulais NOT workingwhen:The time calculated

(i.e. 2:50PM - 3:05PM) = 1hrs, 15 minutesis less than an hour across DIFFERENT hours(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

## 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.