Add 10 hours in TIME Data with Condition

[SkorpionAAM]

I have data 

like this 

12:00 Am

2:00 Am

8:00 Am 

and so on 

what i need on these timing that 10:00 Am is this realy opeing timing of work

so if time start from 00:00:00 to 9:00 Am so add timing to cover 10 Am 

so example if time is 12:00 AM then add 10 hours

if time is 2:00 Am then add 8 hours 

if time is 8:00 Am then Add 2 hours 

i think i explina you guys weel as my english is not good

[Samarth_18]

Hi @SkorpionAAM ,

You can create a column with below code:-

Column = 
VAR diff =
    DATEDIFF ( 'Table (2)'[Column1], "10:00:00 AM", HOUR )
RETURN
    FORMAT ( 'Table (2)'[Column1] + TIME ( diff, 0, 0 ), "hh:mm:ss AM/PM" )

Output:-

Thanks,

Samarth

[Vera_33]

Hi @SkorpionAAM 

Do you need to convert all time to 10am? Then you can simply add a column with value 10am...

Or only for the time between 0am to 10am should be converted to 10am? then like this?

[SkorpionAAM]

only for the time between 0am to 10am should be converted to 10am

[Vera_33 · Accepted solution]

@SkorpionAAM 

Then use the IF statement to check it is between the time period, modify the table/column name accordingly

Column = 
 FORMAT (  IF([Column1]>=TIME(0,0,0)&&[Column1]<=TIME(10,0,0),TIME(10,0,0),[Column1]),"hh:mm:ss AM/PM" )

[SkorpionAAM]

getting error

An argument of function 'TIME' has the wrong data type or the result is too large or too small.

[SkorpionAAM]

thanks but i need to add the time 

so example if time is 12:00 AM then add 10 hours

if time is 2:00 Am then add 8 hours 

if time is 8:00 Am then Add 2 hours 

[Samarth_18]

@SkorpionAAM It is adding the time only. I am hoping your column data type is time only?

[SkorpionAAM]

yes but it will not add the time if  10:00 Am till 23:59:59

 only add timing from 00:00:00 to 9:59:59

[Samarth_18]

@SkorpionAAM What should be ideal output if time is 11:00 AM then do we need to add 23 hours into it?

[SkorpionAAM]

it would be same only Chnage time from 12:00:00 Am till 9:59:59

[Samarth_18]

@SkorpionAAM Actually I am asking what is the expected output if time is 11:00 AM. What it should add into it.

Like you mentioned if it is 8:00 then add 2:00

[SkorpionAAM]

nothing will add after 10 Am they will be same 

t would be same only Chnage time from 12:00:00 Am till 9:59:59 Am

[SkorpionAAM]

yes its Time Datatype

[Samarth_18]

@SkorpionAAM , Can you try again with below code:-

Column = 
VAR diff =
    DATEDIFF ( 'Table (2)'[Column1], "10:00:00 AM", HOUR )
RETURN
    FORMAT ( 'Table (2)'[Column1] + TIME ( diff, 0, 0 ), "hh:mm:ss AM/PM" )

[SkorpionAAM]

same