Forum Discussion
NEGATIVE TIME VALUE
I have a report that is being exported from Global Shop Solutions > Crystal Reports Viewer and then into Excel. I have 2 columns (I and J in the attached file), one of Estimated hours and anther ...
With the default settings, Excel cannot handle negative time.
Excel can handle time if you turn on the 1904 date system, but that will cause all dates to shift by 4 years and 1 day, so you'd have to correct that.
If you want that:
- Select File > Options > Advanced.
- Scroll down to the section 'When calculating this workbook'.
- Tick the check box 'Use 1904 date system'.
- Click OK.
To correct the dates:
- Enter 1462 in an empty cell.
- Select and copy that cell.
- Select the cells with dates.
- Right-click in the selection, then select Paste Special... from the context menu,
- Select Subtract.
- Click OK.
- If necessary, format the cells as dates again.
- Clear the cell in which you entered 1462.
To convert -88.54 etc. to time values:
- Enter the formula =TRUNC(I2)/24+(I2-TRUNC(I2))*100/1440 in another cell in row 2, for example L2.
- Apply the custom number format [h]:mm to this cell.
- Fill down.