convert a custom field

[philky001]

I have a field on that table that in excel is defined as custom H:mm. It is a duration of session field. The data will be

1:00 or 00:30, for one hour or 30 minutes. In the Crystal report it is showing as 12/31/1899.

This is a downloaded excel from a report in the EHR.

 

[Rick Rothstein]

I have a field on that table that in excel is defined as custom H:mm. It is a duration of session field. The data will be

1:00 or 00:30, for one hour or 30 minutes. In the Crystal report it is showing as 12/31/1899.

This is a downloaded excel from a report in the EHR.

In Excel and VBA, dates are stored as floating point numbers... the whole number part is the number of days offset from "date zero" which is theoretically 12/31/1899 in Excel (I say theoretical because the earliest date allowed is 1/1/1900, but those dates are internally calculated as an offset from "date zero")... the time is represented by the decimal part of the number (the fraction of a 24-hour day represented by that time). So, when you put just a time value in the cell, the whole number part is automatically 0 since you are not specifying a date. It appears the Crystal Report software is set to see that field as a date, hence it is reporting "date zero" back to you. I do not know Crystal Report, but it would appear that it needs to be formatted to see that field as a time value, not a date value. There is nothing you need or can do in Excel to change it (unless you are controlling Crystal Reports via VBA code I would guess).