Search code examples
excelpowerpivotdax

Powerpivot Dax- Calculate number of Hours after a fixed time

I have a list of times in a column [Logtime]:

11:45:44PM
07:05:05PM

I'd like to create a measure that returns the total number of hours after 6:30PM. So given the above times:

5.5
0.58

HoursAfter:=[logtime] -6:30PM doesn't work.

Hour[logtime] - hour(18.5) also doesn't work

EDIT: timevalue([logtime]) - timevalue("05:00:00") works but it returns a datetime ala 12/30/1899 5:17:16PM

I need to convert the time 5:17:16 into decimal hours i.e. 5.26, how can I do this?

Solution

  • There may be a slightly more elegant way, but I was able to create a calculated column that does what you want as follows,

    HoursAfter = DATEDIFF(TIMEVALUE("6:30 PM"), Times[LogTime], SECOND) / 3600

    This takes the time difference between 6:30 PM and your LogTime in units of seconds and then converts it to hours by dividing by 60*60 = 3600.

    Edit: A simpler formula can be written as follows,

    HoursAfter = 24 * (Times[LogTime] - TIMEVALUE("6:30 PM"))

    (Multiply by 24 since the datetime values are stored in units of days.)