Has somebody the same problem as me!

if i open with Excel 2007 a DBF file with date fields on it i have as result 
in the excel
sheet the date colum convert in wrong dates formats!

The same DBF file with Excel 2000 or 2003 no problem!

example:

for 30.12.2008 in the DBF fle    (German XP/ Office 2007 and date format)

the result is 12.30.2008  or sometimes 20081230 (both formats in the same 
sheet)

see attach files

Thanks for any help

Willy 

Willy Mollers wrote:

> Has somebody the same problem as me!
> 
> if i open with Excel 2007 a DBF file with date fields on it i have as result 
> in the excel
> sheet the date colum convert in wrong dates formats!
> 
> The same DBF file with Excel 2000 or 2003 no problem!

I whished they (Microsoft) would let the Visual Foxpro Team have a look at
the Excel DBF functionality... seems as if this is getting worse with every
new version of Excel. It would also be very nice if Excel could read memo
fields... now I know why I have to stay away from Office 2007 as long as
possible.

Thomas

I have the same problem with Excel 2007, (but not in Excel 2003)
When you export dbf file to excel, date field must be Character C10,
then you convert date field with ctod(xxx) in export proces..


FUNCTION Preg_Zam()
  LOCAL nIzb

  kretpom1:=0
  kretpom2:="Centralno skladie"

  Vnesi_DATUM()
  if !LastKey()=K_ESC
     //*** najprej naredi pomozno datoteko ***
     dbCreate( "&pot_smeti"+"POMOZNA.dbf",;
                          {{ "RACUN"      ,"C", 7,0 },;
                           { "PARTNER"    ,"C",50,0 },;
                           { "DATUM"      ,"C",10,0 },;     /* DATE FIELD */
                           { "VALUTA"     ,"C",10,0 },;    /* DATE FIELD */
                           { "DAT_PLAC"   ,"C",10,0 },;  /* DATE FIELD */
                           { "ZNESEK"     ,"N",12,2 },;
                           { "PLACANO"    ,"N",12,2 },;
                           { "OSTANEK"    ,"N",12,2 },;
                           { "ZAMUDA"     ,"N",12,0 } } )

     tdOpenDbf("&pot_smeti"+"POMOZNA.dbf","POMOZNA","exclusive")

     nIzb:=tdP2Box("Izpis je mono narediti v RTF obliki primerni za MS 
WORD,",;
                   "ali v XLS obliki primerni ta MS Excel. Izberi obliko 
izpisa ?",;
                   "MS Word","MS Excel",1, {tdWHI,tdDCY},"12.Times New Roman 
CE")

     //*** Odpri datoteke ***
       IF !tdOpenDbf("&pot_baze"+"RACUNI.dbf","RACUNI","exclusive")
          RETURN nil
       ELSE
          //*** Postavljam klju za: RACUNI.DBF - nazivu ***
          TmpCdx()
          index on slo_sort(naziv_par)+dtos(dat_val) to 
"&pot_smeti"+"&cTempDbf" for storno=" " .and.;
                   dat_fak>=oDatum1 .and. dat_fak<=oDatum2 .and. 
sifra_pod=kretpom1 .and. ;
                   dat_pla>dat_val .and. obrok1>0
          set index to "&pot_smeti"+"&cTempDbf"
          dbGoTop()
       ENDIF

     //*** zapisi ***
       cFile:="PREGZAM"
       zbrisi_porocilo(cFile)

       set century off
       set device to print
       set printer to "&pot_report"+cFile

           vrsta:=70;x_stran:=1
           xpartner:=space(40)
           stevc:=0
           x1:=0

           DO while (!eof())
              IF vrsta > max_vrsta
                 glava(cFile)
                 @  4,1 say chr(27)+"M"
                 @  5,5 say chr(27)+"E"+"IZPIS ZAMUJENIH PLAIL v obdobju od 
"+dtoc(oDatum1)+" do "+dtoc(oDatum2)+chr(27)+"F"
                 @  7,5 say replicate("-",90)
                 @  8,5 say "Naziv Partnerja   Datum     Datum     Datum 
t.dni   Znesek        Znesek     RAZLIKA"
                 @  9,5 say "      RAUN      RAUNA    ZAPADL.  PLAILA 
ZAMUDE   RAUNA       PLAILA"
                 @ 10,5 say replicate("-",90)
                 vrsta:=11
                 x_stran:=++x_stran
              ENDIF

              kljuc1:=naziv_par
              IF xpartner=kljuc1
                 x1:=x1+dat_pla-dat_val
                 @ vrsta,11 say sif_rac+"  "+dtoc(dat_fak)+" 
"+dtoc(dat_val)+"  "+dtoc(dat_pla)+;
                                "    "+transform(dat_pla-dat_val,"999")+" 
"+transform(znesek,"999,999.99")+"  "+;
                                "  "+transform(znesek_pla,"999,999.99")+" 
"+transform(znesek-znesek_pla,"999,999.99")
              ELSE
                   vrsta:=++vrsta
                 @ vrsta,5 say chr(27)+"E"+naziv_par+chr(27)+"F"
                   vrsta:=++vrsta
                   x1:=x1+dat_pla-dat_val
                 @ vrsta,11 say sif_rac+"  "+dtoc(dat_fak)+" 
"+dtoc(dat_val)+"  "+dtoc(dat_pla)+;
                                "    "+transform(dat_pla-dat_val,"999")+" 
"+transform(znesek,"999,999.99")+"  "+;
                                "  "+transform(znesek_pla,"999,999.99")+" 
"+transform(znesek-znesek_pla,"999,999.99")
              ENDIF

              //*** EXPORT TO Excel
                IF nIzb=2
                    SET CENTURY ON
                    a1:=sif_rac
                    a2:=dat_fak
                    a3:=dat_val
                    a4:=dat_pla
                    a5:=znesek
                    a6:=znesek_pla
                    a7:=znesek-znesek_pla
                    a8:=dat_pla-dat_val
                    SELECT POMOZNA
                      Append Blank
                      Replace RACUN    with a1,;
                              PARTNER  with xPartner,;
                              DATUM    with dtoc(a2),;     /* DATE FIELD */
                              VALUTA   with dtoc(a3),;    /* DATE FIELD */
                              DAT_PLAC with dtoc(a4),;  /* DATE FIELD */
                              ZNESEK   with a5,;
                              PLACANO  with a6,;
                              OSTANEK  with a7,;
                              ZAMUDA   with a8
                    SELECT RACUNI
                    SET CENTURY OFF
                ENDIF

              xpartner:=naziv_par
              stevc:=++stevc
              vrsta:=++vrsta
              dbskip()
           ENDDO

             vrsta:=++vrsta
           @ vrsta,5 say replicate("=",90)
             vrsta:=++vrsta+1
           @ vrsta,5 say "TEVILO ZAMUJENIH PLAIL : 
"+transform(stevc,"99999")
             vrsta:=++vrsta
           @ vrsta,5 say "POVPRENO ZAMUJENO DNI   : 
"+transform(x1/stevc,"99999")
             vrsta:=++vrsta
           @ vrsta,1 say " "

       set century on
       set printer to
       set device to screen

       if nIzb=1
          //*** prenos v RTF ***
          Tiskaj(cFile)
       else
          //*** prenos v Excel ***
          SELECT POMOZNA
          Copy2Excel("Preg_Zamude")
       endif

       dbcloseall()
  endif
RETURN nil


This works for me (for now)
Best regards
Zupan Miran
Slovenia

Hello Zupan,

thanks for your answer

If i understand your program the destination colum for date in Excel is
formated text only   ("31.02.2008") !

What i need is that the "date destination colum" in Excel is formated as
date field, the program
that use this created file can only import the data if the field is a real
Excel date format.
I dont have the possibility to change this program myself .

Greetings

Willy

"Zupan Miran" <spczupan@spc-zupan.si> schrieb im Newsbeitrag 
news:6b7a179$56717429$7c1@news.alaska-software.com...
> I have the same problem with Excel 2007, (but not in Excel 2003)
> When you export dbf file to excel, date field must be Character C10,
> then you convert date field with ctod(xxx) in export proces..
>
>
> FUNCTION Preg_Zam()
>  LOCAL nIzb
>
>  kretpom1:=0
>  kretpom2:="Centralno skladise"
>
>  Vnesi_DATUM()
>  if !LastKey()=K_ESC
>     //*** najprej naredi pomozno datoteko ***
>     dbCreate( "&pot_smeti"+"POMOZNA.dbf",;
>                          {{ "RACUN"      ,"C", 7,0 },;
>                           { "PARTNER"    ,"C",50,0 },;
>                           { "DATUM"      ,"C",10,0 },;     /* DATE FIELD 
> */
>                           { "VALUTA"     ,"C",10,0 },;    /* DATE FIELD */
>                           { "DAT_PLAC"   ,"C",10,0 },;  /* DATE FIELD */
>                           { "ZNESEK"     ,"N",12,2 },;
>                           { "PLACANO"    ,"N",12,2 },;
>                           { "OSTANEK"    ,"N",12,2 },;
>                           { "ZAMUDA"     ,"N",12,0 } } )
>
>     tdOpenDbf("&pot_smeti"+"POMOZNA.dbf","POMOZNA","exclusive")
>
>     nIzb:=tdP2Box("Izpis je mozno narediti v RTF obliki primerni za MS 
> WORD,",;
>                   "ali v XLS obliki primerni ta MS Excel. Izberi obliko 
> izpisa ?",;
>                   "MS Word","MS Excel",1, {tdWHI,tdDCY},"12.Times New 
> Roman CE")
>
>     //*** Odpri datoteke ***
>       IF !tdOpenDbf("&pot_baze"+"RACUNI.dbf","RACUNI","exclusive")
>          RETURN nil
>       ELSE
>          //*** Postavljam klju za: RACUNI.DBF - nazivu ***
>          TmpCdx()
>          index on slo_sort(naziv_par)+dtos(dat_val) to 
> "&pot_smeti"+"&cTempDbf" for storno=" " .and.;
>                   dat_fak>=oDatum1 .and. dat_fak<=oDatum2 .and. 
> sifra_pod=kretpom1 .and. ;
>                   dat_pla>dat_val .and. obrok1>0
>          set index to "&pot_smeti"+"&cTempDbf"
>          dbGoTop()
>       ENDIF
>
>     //*** zapisi ***
>       cFile:="PREGZAM"
>       zbrisi_porocilo(cFile)
>
>       set century off
>       set device to print
>       set printer to "&pot_report"+cFile
>
>           vrsta:=70;x_stran:=1
>           xpartner:=space(40)
>           stevc:=0
>           x1:=0
>
>           DO while (!eof())
>              IF vrsta > max_vrsta
>                 glava(cFile)
>                 @  4,1 say chr(27)+"M"
>                 @  5,5 say chr(27)+"E"+"IZPIS ZAMUJENIH PLAIL v obdobju 
> od "+dtoc(oDatum1)+" do "+dtoc(oDatum2)+chr(27)+"F"
>                 @  7,5 say replicate("-",90)
>                 @  8,5 say "Naziv Partnerja   Datum     Datum     Datum 
> St.dni   Znesek        Znesek     RAZLIKA"
>                 @  9,5 say "      RAUN      RAUNA    ZAPADL.  PLAILA 
> ZAMUDE   RAUNA       PLAILA"
>                 @ 10,5 say replicate("-",90)
>                 vrsta:=11
>                 x_stran:=++x_stran
>              ENDIF
>
>              kljuc1:=naziv_par
>              IF xpartner=kljuc1
>                 x1:=x1+dat_pla-dat_val
>                 @ vrsta,11 say sif_rac+"  "+dtoc(dat_fak)+" 
> "+dtoc(dat_val)+"  "+dtoc(dat_pla)+;
>                                "    "+transform(dat_pla-dat_val,"999")+" 
> "+transform(znesek,"999,999.99")+"  "+;
>                                "  "+transform(znesek_pla,"999,999.99")+" 
> "+transform(znesek-znesek_pla,"999,999.99")
>              ELSE
>                   vrsta:=++vrsta
>                 @ vrsta,5 say chr(27)+"E"+naziv_par+chr(27)+"F"
>                   vrsta:=++vrsta
>                   x1:=x1+dat_pla-dat_val
>                 @ vrsta,11 say sif_rac+"  "+dtoc(dat_fak)+" 
> "+dtoc(dat_val)+"  "+dtoc(dat_pla)+;
>                                "    "+transform(dat_pla-dat_val,"999")+" 
> "+transform(znesek,"999,999.99")+"  "+;
>                                "  "+transform(znesek_pla,"999,999.99")+" 
> "+transform(znesek-znesek_pla,"999,999.99")
>              ENDIF
>
>              //*** EXPORT TO Excel
>                IF nIzb=2
>                    SET CENTURY ON
>                    a1:=sif_rac
>                    a2:=dat_fak
>                    a3:=dat_val
>                    a4:=dat_pla
>                    a5:=znesek
>                    a6:=znesek_pla
>                    a7:=znesek-znesek_pla
>                    a8:=dat_pla-dat_val
>                    SELECT POMOZNA
>                      Append Blank
>                      Replace RACUN    with a1,;
>                              PARTNER  with xPartner,;
>                              DATUM    with dtoc(a2),;     /* DATE FIELD */
>                              VALUTA   with dtoc(a3),;    /* DATE FIELD */
>                              DAT_PLAC with dtoc(a4),;  /* DATE FIELD */
>                              ZNESEK   with a5,;
>                              PLACANO  with a6,;
>                              OSTANEK  with a7,;
>                              ZAMUDA   with a8
>                    SELECT RACUNI
>                    SET CENTURY OFF
>                ENDIF
>
>              xpartner:=naziv_par
>              stevc:=++stevc
>              vrsta:=++vrsta
>              dbskip()
>           ENDDO
>
>             vrsta:=++vrsta
>           @ vrsta,5 say replicate("=",90)
>             vrsta:=++vrsta+1
>           @ vrsta,5 say "STEVILO ZAMUJENIH PLAIL : 
> "+transform(stevc,"99999")
>             vrsta:=++vrsta
>           @ vrsta,5 say "POVPRENO ZAMUJENO DNI   : 
> "+transform(x1/stevc,"99999")
>             vrsta:=++vrsta
>           @ vrsta,1 say " "
>
>       set century on
>       set printer to
>       set device to screen
>
>       if nIzb=1
>          //*** prenos v RTF ***
>          Tiskaj(cFile)
>       else
>          //*** prenos v Excel ***
>          SELECT POMOZNA
>          Copy2Excel("Preg_Zamude")
>       endif
>
>       dbcloseall()
>  endif
> RETURN nil
>
>
> This works for me (for now)
> Best regards
> Zupan Miran
> Slovenia
>
>

Willy Mollers wrote:

> What i need is that the "date destination colum" in Excel is formated as
> date field, the program
> that use this created file can only import the data if the field is a real
> Excel date format.

You could use the "text to columns" assistant to manually (or via macro)
convert the column to date format - just as a workaround.

Thomas

Zupan Miran wrote:

>  I have the same problem with Excel 2007, (but not in Excel 2003)
> When you export dbf file to excel, date field must be Character C10,
> then you convert date field with ctod(xxx) in export proces..

This creates an potential additional problem if the data is to be shared
between people with differend date formats, as Excel uses the systems date
setting when interpreting date strings.

Actually, the right thing to do would be to open a support case with
Microsoft, since it is their fault.

Thomas

Excel stores the date as the number of days since 1900-Jan-0.
1 = 1900-Jan-01

For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days 
have passed since 1900-Jan-0.

So for any date after 2000-Jan-19...

nDate := (MyDbf->MyDate - StoD("20000119"))+36544

Pass nDate to Excel and format the Column or Cell as Date

Greg Doran, Dublin Ireland