ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Field in EXCEL Converting to Date

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Field in EXCEL Converting to Date

    I am sending data to an EXCEL spreadsheet and then on to a user. The field is a customer number with the value of 2MAY01. EXCEL thinks it is a date but I want it to default as text. I seem to remember something a while back about this but I've slept a few times and cannot remember.

    Is there a character of something that can be put in front of the customer number data to force EXCEL to not think it is a date?

    Thanks!

  • #2
    How are you creating the spreadsheet?

    Comment


    • #3
      Assuming it is a csv file try something like
      ="2MAY01" instead of "2MAY01"

      That is put a = sign in front of the string.

      It might work.

      Comment


      • #4
        I know this sounds like a cliche but I am asking for a colleague so my replies may be delayed a bit.

        Scott... he is using CPYTOIMPF. I have just asked him about the STRDLM paramater of *DBLQUOTE.

        Peder... Thanks! I'll pass that along and see if it helps!

        Comment


        • #5
          How are you "sending" data to Excel? If possible, put a leading single quote in front the text, so Excel will treat it as text.

          '2MAY01

          Ringer

          Comment


          • #6
            I think as long as you use something like CPYTOIMPF you are going to be very limited in what you can do. In this case, Excel simply "tries" to figure out what the value is -- it has to guess. If it seems like a date, it'll make it a date. Very hard to control anything since its not possible to specify anything.

            If you create this using an approach that creates an actual spreadsheet rather than a CSV file, you'll be able to tell it the proper formatting for the cell, so you won't have these limitations.

            Comment


            • #7
              He is going to go with the " ' " mark in front of the data. The end user is fussing a little bit about the extra character but it is all good!

              Thanks everyone for the suggestions!!!!

              Comment

              Working...
              X