Forum Discussion

raghu25's avatar
raghu25
Copper Contributor
Aug 24, 2020

Excel Data Extraction with condition.

Hi All 

 

I am Working on the Excel data where I created column based on the value's from other colum as shown below.

 
 

 

2hrLoadEndTime is as New Column created with if condition =IF(AND(ISBLANK([@[End Time]]),[@[Load Status-1]] = "Skipped"),"entervalue",VALUE([@[End Time]])).

 

what am looking is if End time column is empty i need to get the end time of the next hour record.

like in above table Start Time of 07:00 hrly record have empty End Time so i need to put the next Hourly (08:00 hrly) end time in the new colum(2hrLoadEndTime)

 

Please give some suggestions.

 

 

 

2 Replies

  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Bronze Contributor
    Aug 24, 2020

    raghu25 

    =IF(AND(ISBLANK([@[End Time]]),[@[Load Status-1]] = "Skipped"),[@[Start Time]]+(1/24),VALUE([@[End Time]]))

    or

    =IF(AND(ISBLANK([@[End Time]]),[@[Load Status-1]] = "Skipped"),[@[Start Time]]+"1:00",VALUE([@[End Time]]))