KuraiChikara
Board Regular
- Joined
- Nov 16, 2016
- Messages
- 111
- Office Version
- 2013
- Platform
- Windows
Hello,
I know how to convert time say ET to MT but my question is on the process on WHY Excel does what it does.
Example:
A1 you have ET and B1 you have the MT formula conversion. Using this simple formula will work, but not for all time frames:
=A1-"02:00"
It can do AM to AM and PM to AM: IE 18:00 turns into 16:00, 07:00 turns into 05:00.
What it CANT do, is go from AM to PM: IE 01:00 ET will return ############# instead of 23:00 MT
To fix that, the formula just gets a little more detailed IE:
=24+A1-"02:00"
This fixes the problem, instead of a never ending sequence of # symbols, it returns 23:00.
My question is, why do the ###### come back as a result with the first formula? This is not a data length issue because no matter how far I stretch the cell, the pound symbols don't go away, so what is that error called? It's not #DIV.0!, #N/A, #NA ME?, #NULL !, #NUM !, #REF ! or #VALUE ! yet it doesnt return any data.
I know how to convert time say ET to MT but my question is on the process on WHY Excel does what it does.
Example:
A1 you have ET and B1 you have the MT formula conversion. Using this simple formula will work, but not for all time frames:
=A1-"02:00"
It can do AM to AM and PM to AM: IE 18:00 turns into 16:00, 07:00 turns into 05:00.
What it CANT do, is go from AM to PM: IE 01:00 ET will return ############# instead of 23:00 MT
To fix that, the formula just gets a little more detailed IE:
=24+A1-"02:00"
This fixes the problem, instead of a never ending sequence of # symbols, it returns 23:00.
My question is, why do the ###### come back as a result with the first formula? This is not a data length issue because no matter how far I stretch the cell, the pound symbols don't go away, so what is that error called? It's not #DIV.0!, #N/A, #NA ME?, #NULL !, #NUM !, #REF ! or #VALUE ! yet it doesnt return any data.
