How to fix ######## and correctly display negative time values in CrewCenter Excel exports

  • Updated

Overview

The article describes a display issue that is caused by Microsoft Excel: Excel cannot correctly display negative time values when using the h:mm time format. The recommended workaround is to use the decimal h,mm format instead, which avoids the Excel limitation and correctly shows negative time values in CrewCenter exports.

If your Excel export shows ############################ for time values:
 

 

Some CrewCenter exports contain negative time values, which is expected in real working scenarios. For example, if the required working time is 8 hours and the actual worked time is 2 hours, the resulting balance will be –6 hours.
 
The data itself in the document is correct, but Excel is using the wrong date system (Excel  cannot display it properly with the default setting.). To display the values properly, enable the 1904 date system for that specific file and save it.

That -6:00 value is correct and must stay visible in Excel.

Why switching to the 1904 date system fixes this

Enabling the 1904 date system tells Excel how to correctly display:

  • Time balances
  • Missing work hours
  • Negative durations

After enabling it:

  • –6:00 is displayed correctly
  • No ############################ values appear
  • Calculations work as expected

This is why CrewCenter and Fieldwire exports that include time balances require this setting.

Important notes (please read)

This setting cannot be applied globally to all Excel files and many other Excel documents use the default date system.

Switching the date system in those files may make dates look wrong.

This means: You must change the setting for each exported file individually.

Good news: You only need to set this once per exported document. After saving, the setting stays correct for that document.

How to change the setting (Windows)

Once you open the exported Excel file:

  1. Click File
     

     

  2. Click Options
     

     

  3. Click Advanced
  4. Scroll to When calculating this workbook

  5. Check Use 1904 date system
     

     

  6. Click OK
  7. Save the file

How to change the setting (Mac)

Once you open the exported Excel file:

  1. Click Excel in the top menu
  2. Click Preferences


     

  3. Click Calculation

     


     

  4. Scroll down to "When Calculating Workbooks" options and check Use 1904 date system

     


     

  5. Close the settings window
  6. Save the file (If the document is not saved, the process will need to be repeated the next time the document is opened.)

For more detailed technical information, please refer to the official Microsoft article: Date systems in Excel
 

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request