Convert a Date Time field to a Time Field

Question

Hello,

I have been asked to run a report to look at calls being logged out of hours (from 8pm to 6am) and I am trying to create a calculated field to remove the date from the date time field out so I can apply a filter to it. The default server format of the field is mm/dd/yyyy hh:mm:ss AM/PM. I have tried using =right to get the time part only but I think its coming out as the wrong format because when I apply the filter I'm not getting back any results.

I'm not sure if this is the correct way to do what I'm trying to do, so I'm open to other suggestions :) Any help would be greatly appreciated.

Thanks

Suzi

Answer 1

Hi De Dancey,

for getting the format for time, this can be done variety of way. e.g

• string.Format("{0:t}",Today)   -   12:00 AM
• TimeValue(Today)   -  12:00:00 AM

---

Regards Harsh

Answer 2

Hi Harsh,

Thanks for the above. I think the issue is actually with the filtering of the data. Applying a filter as a date/time doesn't work because it wants a date entered in (and if I leave one out it enters one randomly) and It can't process the filter as a string. I amended the filter to read between 20:00 and 23:59 and it worked. It is falling over wrying to go from 20:00 through to 06:00. Hope this makes sense?!?

Thanks

Suzi

Answer 3

This is the approach I will take.

1. First thing is to understand your time boundaries in minutes.

20:00 (8pm) to 23:59 (before midnight) = 1200 minutes to 1380 minutes

00:00 (midnight) to 06:00 AM = 0 minutes to 360 minutes

2. Add a calculated field "CallMinutes" and convert your date (call time)  to minutes using this expression = Hour(Fields!CallTime.Value)*60 + Minute(Fields!CallTime.Value).

This will give you the equivalent minutes of the call.

3. You then use this calculated field "CallMinutes" as your filter.

=SUM(IIF(((Fields!CallMinutes.Value>=0 and Fields!CallMinutes.Value<=360) OR (Fields!CallMinutes.Value>=1200 and Fields!CallMinutes.Value<=1380)),1,0))

The above will give you the count of calls logged in between 8PM to 6AM

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 4

Hi Krootz,

 Amazing! Thank you so much :)