Converting Period Points to Date and Time

[jtc]

Hello,

I have some data beginning on 1/1/2019 at 00h00. That date and time are given the number 1. The next data point is at 1/1/2019 at 01h00 associated with the number 2, and so on.

I'm looking for a way to convert each number associated with a date and time to the real date and time.

Does anyone have an idea?

Thanks

[Anonymous · Accepted solution]

HI @jtc ,

Did you means you have list of number records, their have a initialization datetime and these numbers means to hour number and you want to convert them to datetime format based on initialization and offset?

If this is a case, you can add a calculate column with below formula to achieve your requirement:

Datetime =
VAR init =
    DATE ( 2019, 1, 1 )
VAR offsetDay =
    INT ( ( [Value] - 1 ) / 24 )
VAR offsetHour =
    MOD ( [Value] - 1, 24 )
RETURN
    DATE ( YEAR ( init ), MONTH ( init ), DAY ( init ) + offsetDay )
        + TIME ( offsetHour, 0, 0 )

Regards,

Xiaoxin Sheng

[jtc]

Thank you @Anonymous ,

It's exactly that.

Best regards