UTC time to CST time equation

It is difficult to debug a picture, but I am unable to replicate your #Value error result. The indicated formulas all work fine for me with a few different inputs.

My guess is that your time stamps are text and not numbers (they appear to be left aligned in the cell, which is the default for text. Numbers default to right aligned), and there is something about that text string that prevents Excel from automatically recognizing the times and converting them to numbers. The #VALUE error is then an indication that Excel does not know how to add a number to a text string.

As indicated, we cannot debug a picture. If I were to debug this, I would start with an ISTEXT() function to see if the time stamps are numbers or text. If they are text, I would then work on converting them to numbers. Strategies here for converting text to numbers: https://support.microsoft.com/en-us/...rs=en-us&ad=us

Once the timestamps are converted to numbers, I would expect your formulas to work without #VALUE errors.

Are you sure the values in column A are actually valid times rather than strings?

You can check that using the ISNUMBER function, it will return FALSE if they aren't time values.

If they do turn out to be strings you could use this formula to convert them to CST,

Formula:

Please Login or Register  to view this content.

though it would probably be better to convert the entire column from strings to time values.

To do that select the column, go to Data>Text to columns.., on the third step select Date in the column data format section and click Finish.

sgreeder,
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.