Keeping cell formatting when using offsets to change data view

[russell]

Hi,

Ive had a look around and cant find anything to answer this so apologies if its been asked before.


i have a table of data, column one is numeric, two and three are times (hh:mm:ss) and 4 is numeric.


im using offsets to retrieve the data where i change the offset to look at each column as required.

The issue i have is that the cells i am moving the info to dont keep it in the same format, so its either numbers as time or time as numbers as i move through the columns (i have to keep setting the format in the cells!). is there any way round this? thanks.

 

[Faseeh]

Hi russell,


Which type of formatting you have been using for Time. You are reporting for 24 Hrs or using AM/PM? Can I have a sample sheet? Where you will be using the final output and finally will you be retrieving value for a single cell or you will copy this formula across the column?


Regards,

FASEEH

 

[SirJB7]

Hi, russell!

What range are the numeric data in columns 1 & 4?

I ask that because Excel stores dates and times as numeric values, the date part is the integer part (relatively based to 01/01/1900 as 0) and the time part is the decimal part (from 0 to 23:59:59, ranging from 0 to 1).

So, if your numeric data are greater than 1 or negative, you can manage to handle different formats upon the cell values: <0 numeric format, >=0 and <=1 date, >1 numeric.

Regards!

 

[russell]

Sir JB, this sounds like exactly whats needed, how do i set this up?

im too used to just clicking on the cell then changing the format that i didnt realise there was a way to contol this based on value.


thanks for the help!

 

[SirJB7]

Hi, russell!

Just assign this format to the cells:

[<0]-#.##0;[<1]hh:mm:ss;#.##0

Adjust thousands "." and list ";" separators to your local settings.

Regards!