Calculating time difference using only hh:mm fields

[Rosanne Pogash]

Does anyone know if there is a way to find the difference between times for two fields that are defined as HH:MM fields?  I have two MyCap forms where the particpant enters the time he started eating and the time he stopped eating.  Both these fields are defined as HH:MM fields. I need to know the length of eating time for each day.

I know how to do a datediff equation, but the fields needs to defined as a datetime format. I don't want to define the fields in MyCap as datetime because it is too easy for the participant to enter the wrong date when entering overdue tasks.

Since the schedule date is populated in the MyCap form, is there a way to combine a date field and a time field into a new variable that is datetime?  If so, I can then use the datediff function to determine the time length of eating each day.

Thanks,

Rosanne

[Amit Tandon]

Did you try concatenating the date field with time filed and then suing datediff

[kenm...@gmail.com]

I just ran into this very same issue as I was requested to capture the average bedtime and average wake-time for participants and then calculate the time in between. I ended up creating hidden fields to convert the times to decimal values as follows:

bedtime

@CALCTEXT( (left([typical_bedtime],2)*1.0) + ((right([typical_bedtime],2)*1.0)/60.0))

waketime

@CALCTEXT( (left([typical_waketime],2)*1.0) + ((right([typical_waketime],2)*1.0)/60.0))

The next challenging thing was that the average bedtime could be after midnight, so to calculate the time between these two times I used the formula:

sleep_time

@CALCTEXT(if([bedtime]*1.0>[waketime]*1.0, (24.0-[bedtime]*1.0) + [waketime]*1.0, [waketime]*1.0 - [bedtime]*1.0))

To convert this back into a HH:MM field:

@CALCTEXT(concat( right(concat('00', rounddown([sleep_time],0)),2),
                 ':',
                 left(round((([sleep_time]-rounddown([sleep_time],0))*60),0),2)))

Hope this helps...

[kenm...@gmail.com]

Correction for the last calculation:

@CALCTEXT(concat( right(concat('00', rounddown([sleep_time],0)),2),
                 ':',

                 right(concat('00',left(round((([sleep_time]-rounddown([sleep_time],0))*60),0),2)),2)))

Testing showed that the MM part of a Time variable also needed to be zero padded to fill the minutes field.