For the purpose of the demonstration, in an Excel spreadsheet, let’s say we have a date-time value of “9/13/14 3:02 PM” in cell A2 and “9/13/14 5:43 PM” in cell B2. Even though the screen displays them in a format friendly to me at my location in the United States, in the background Excel actually treats them as a decimal number that is universal regardless of the display formatting. In this case the two values are actually 41895.63
and 41895.74, respectively; the details on this number is outside the scope of this particular article. Getting back on track, in order to calculate the number of days, hours, or minutes that has elapsed between these two values, we can simply use these formulas:
Difference in days: =(B2-A2)
Difference in hours: =(B2-A2)*24
Difference in seconds: =(B2-A2)*24*60
I often use CSV format to transport data, largely because its text nature makes the data extremely portable across different platforms and applications. Yesterday when a colleague opened a CSV file from me using Excel, one of the fields I provided was displayed incorrectly. Two examples of the troubles Excel are listed below.
|Value Provided in CSV
||Shown by Excel
In the first case, Excel thought the value was a scientific notion; in the second case, Excel treated it as a number. In both cases, they were both wrong.
This issue also happens frequently when displaying ZIP codes for addresses in the United States, eg. Excel showing the intended “04001” as “4001”.
Because this is an Excel-specific behavior, I consider the following fix to be a work-around rather than a permanent solution; nevertheless, it works well for me. Instead of a simple CSV format as I had originally:
I inserted an equal sign in front of it. This forces Excel to think that I am writing a formula that outputs a string, thus solving the Excel issue.