# Convert DAX date and time to rounded down hour



## tbobolz (Mar 18, 2014)

Excel 2010

Hi I'm having trouble converting an date and time field formatted like this ( 12/9/2006 4:41:00 PM) to extract only the hour (rounded down) So this example should return 16:00. 

My date and time field is labeled "ER ARRIVAL DATETIME"

I have time several differnet items I have found but I am unable to get them to round down. 

Thanks for any help you can offer.

Terry


----------



## mole999 (Mar 18, 2014)

can you fake it by using just the [h] value and adding :00. you could just use =CONCATENATE(TEXT(cell,"hh"),":00")


----------



## tbobolz (Mar 18, 2014)

Tanks Mole999, I have tried various verison of this thought, yet it does not work, or if I do get it to work, it returns the column formatted in "general" format and will not allow the conversion back to date.


----------



## mole999 (Mar 18, 2014)

did you want the Date and hour like

=CONCATENATE(TEXT(G16,"mm/d/yyyy hh"),":00")


----------



## tbobolz (Mar 18, 2014)

I actual just wish to extract the Hour from the the date and time. DAX will not load the cell value after a concatenate(text command.


----------



## tbobolz (Mar 18, 2014)

Ok, finally found a solution.   I used  =concatenate(hour(Table1[ER ARRIVAL DATETIME]),":00").   Most likely a better way out there, but I can now move forward.


----------

