Convert DAX date and time to rounded down hour

[tbobolz]

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]

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

 

[tbobolz]

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]

did you want the Date and hour like

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

 

[tbobolz]

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]

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.