convert a custom field

philky001

Board Regular
Joined
Jun 8, 2005
Messages
129
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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).
 
Upvote 0

Forum statistics

Threads
1,224,746
Messages
6,180,705
Members
452,994
Latest member
Janick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top