NEGATIVE TIME VALUE

Occasional Contributor

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 of Actual hours.  There are a few values of negative time--a few, relative to the whole couple hundred thousand rows

 

Does anyone have a method for maintaining the negative value?  For instance, the top row comes in as -88.54, when it should be -88:54 (negative 88 hours and 54 minutes).

 

As a bonus, does anyone have a method for translating the decimal to a colon for both positive & negative in the same step?  I currently have the positive values translated accurately.  Just struggling with these negative values now.

1 Reply

@Lorenski 

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.

S1957.png

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.