cancel
Showing results for 
Search instead for 
Did you mean: 

Incorrect date format in excel reports

Former Member
0 Kudos

Hi,

Some Activity exported from Activity overview to excel have wrong date format. For example

Some have 21/04/2008 u2013 Correct

Some have 13/04/09 u2013 Incorrect

All the dateu2019s in the database seems to be correct like '2008-04-13 00:00:00.000'.

Client is using SAP B1 2005 SP01 PL-48. Is there any know issue?

I tried the same in B1 2007 upgrading the database. All the date seems to be correct when I export it here.

What may be the reason?

Any suggestions will be appreciated.

Regards,

Mohan

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

You have to define 4 digit year format both in B1 and OS in order to solve your problem.

Former Member
0 Kudos

Hi

After you export to excel, Go to that particular row where date are display, There you select that row and right clike mouse-Formet Cell-under Number tab-select date then you choose which format you want then press OK

Date format in excel gives correct format.

Prasad

Former Member
0 Kudos

Hi Prasad,

Only certain dates in the column respond to this action, rest of them (Problematic date) did not respond.

One thing I notice is, if I click the problematic date in excel 2007 it comes up with a warning message indicating the cell is currently u2018Text Date with 2- digit yearu2019, if I change it to u2018edit in Formula baru2019 the problem disappear for that cell.

Is this a problem to do with excel or the date exported from B1?

Regards,

Mohan

Former Member
0 Kudos

Check some other available date formats in B1, such as MM/DD/CCYY.

Former Member
0 Kudos

Hi Gordon,

It took some time for me to verify in the client machine.

I have changed the date format in both Business One and in the regional settings to DD/MM/YY. It seems to be ok however the appearances of date column in excel seems to be uneven.

04/10/09

04/11/09

  • 13/04/09

  • 14/04/09

  • 19/04/09

(Ignore the *)

I think, the problematic dates seem to be exported to excel in the paste special text format.

Any Idea why this is happening?

Regards,

Mohan

Edited by: Mohanakrishnan T on May 5, 2009 1:09 PM

Edited by: Mohanakrishnan T on May 5, 2009 1:10 PM

Edited by: Mohanakrishnan T on May 5, 2009 1:11 PM

Edited by: Mohanakrishnan T on May 5, 2009 1:11 PM

Former Member
0 Kudos

We have the same problem, here's a workaround for queries, it will convert the date to a text string.

CONVERT(nchar(8), T0.DocDate, 112) AS DATE

Former Member
0 Kudos

Hi Jenkins,

Thanks for the query, but How can I use this query during excel export?

Regards,

Mohan

Edited by: Mohanakrishnan T on May 5, 2009 1:32 PM

Former Member
0 Kudos

You need to make sure OS date format match B1 data format. B1 format is under Admin - Sys Init - General Setting - Display. OS format is under Control Panel - Regional and Language Optios.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Thanks for the response!

Both the date format is same,

I am a bit confused concerning your suggestion. As I mentioned earlier, I get 2 different date format. If OS and B1 dates are different either the year should be u20180000u2019 or u201800u2019, how come both?

Any other suggestions

Regards

Mohan