Times in multiple columns

[shrijan]

Hi all gurus,

I am trying to find a formula to capture Time From and Time To in two columns (see columns A & B in the attached). However, the challenge is, my time data are captured in different columns (due to the way data is structured). is there a formula to be able to capture time from different columns?

Appreciate the help

[kersplash]

You could use helper columns containing the final Time 1 & Time 2, and this would contain an IF statement checking cells for blanks and displaying the final time.
e.g. for Time 1 to check the first 2 Time 1 cells in C4;

Please Login or Register  to view this content.

And copy down to C7 to show you the concept.

Then you would do your calculation from this one cell.

[shrijan]

Thanks for this, however, not all cells are empty, some of them have data in them .

S

[kersplash]

Maybe you then need to show in your sample what the "real" data looks like.

[sandy666]

If you are able to use PowerQuery
with your current example
- load marked range to PQ Editor
- merge datetime columns with space delimiter
- split merged columns by space delimiter
- remove unnecessary columns and stay with two time columns
- do cosmetic changes - header names
- load this table to the sheet

[shrijan]

hi all,

reattaching the file again for clarity.

S

[kersplash]

Once again is that a "real" representation of your data, ie. all the text is the same except for Column F which has all the same text in itself?

[shrijan]

Not really, as cannot publish real data due to confidentiality. however, the data could be in different formats.

[sandy666]

once again
Are you able to use PowerQuery?

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel