DATE/TIME format formula- need help

[kg2report]

Hi.

I have field that is formatted as mm/dd/yyyy HH:MM:SS pm/am. I need to delete the seconds but still be able to show the "am/pm". Please help.


Thanks.

kg

 

[kray4660]

For display purposes you can format at datetime field a number of ways. Right click on the field in the report and Format Field. In the Format Editor. In the Date and Time tab, click on Customize, Then click on the Time tab. At that point you should be able to customize the time in a variety of ways.

 

[kg2report]

I tried that already before and that didn't work.

 

[kray4660]

In what way did it not work. Maybe it is version related. But at least Crystal Reports 2016 allows almost full formatting of a date time field.

 

[kg2report]

Even though it seems fixed in Crystal, when it's exported to excel, the seconds data remains.

 

[SkipVought]

Hi,

Regarding Excel (and ALL computer systems for that matter) DATE/TIME values are pure numbers like 47835.25.

In Excel, the INTEGER part of the number refers to the DATE, the number being days since December 31, 1899. The DECIMAL part of the number is the TIME as a fraction of a day.

In Excel, as in other applications, the Date/Time can be FORMATTED in a myriad of ways. But Formatting a Date/Time value does nothing to the underlying value (like the 47835.25). So, if you have hours:minutes:seconds showing up, you have a Decimal value in your Date/Time Value. You can choose to leave the Decimal portion there and just FORMAT the Date/Time value to only display the DATE part (not a particularly good idea if you plan to CALCULATE using your Dates) or you can CHANGE the Date/Time Value to an INTEGER alone (a better idea), using the INT() function.

Further Excel-related questions can be asked in Forum68.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kray4660]

Also (unfortunately), when Crystal Reports exports to Excel. A lot of formatting can be lost (other than just date/times).

 

[SkipVought]

Certain “formatting” is application specific. It’s a fact of life!

When I exported data from CR or BO, I NEVER exported a report, since reports had all sorts of unwanted features that makes the data virtually as useful as a paper report.

I ALWAYS exported DATA TABLES as TEXT in a .csv file and IMPORTED (rather than OPENED) into Excel, where I have control over the Data Types for each column. So I NEVER had the problems you reported like “A lot of formatting ... lost (other than just date/times).”

Maybe you could detail the problems you’re having and post a sample of the formatted data that gets lost. Perhaps I can help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]