convert time of day to a number

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I have 2 fields - Start Time and End Time - both are Time of Day Fields.

I am trying to create a crosstab schedule report where the EE's name is on the left, and group columns of dates and Start /End time. I can only do the Start and End times in a crosstab report if the times are converted to numbers.

Here is the formula I have used:

ToNumber(ToText([Start Time]))

It converts 9:00 am to 9, but doesn't convert 5:30 pm to 5.5 or 5.3 - returns only 5.00. I need it to convert to 5.50 or 5.30. Any idea?

Photo of Andrew

Andrew

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
Try this for the formula:

Hour([Start Time]) + Minute([Start Time]) / 100


This converts:

[Start Time] = 9:00am => 9

[Start Time] = 5:35pm => 17.35


You can adjust the formula if you need the number to come out differently (ignore AM/PM, etc.)
Photo of Jon Elliott

Jon Elliott

  • 60 Points
simple and elegant!