I am working on a turn around time report and this calculation works fine as long as the Date/Time data is stored as a Date/Time field (separate fields):
DateDiff ("s",DateTimeValue ({Episodes.DateColl},{Episodes.TimeColl} ),
DateTimeValue ({Episodes.ReportDate},{Episodes.ReportTime} ))/60/60
The output is reported in hours.
The Time Collected field does not store the time in the 00:00 AM/PM format. This time is stored as text since there are instances when the time is not known and the default is 0000 (4 zeros). The TimeColl field is 4 character string in Military time.
I need to come up with a formula that converts the TimeColl into a field that is compatible with the formula. Is there any way to do this? or should I go about it a different way?
IP Logged
