Posts by Mats Carlsson

    Re: Date conversion

    Oh I feel so stupid! I goofed when translating your solution to the Swedish language Excel version I use.

    Thanks Domenic your solution works perfect! It couldn't get better. You are really clever!

    Thanks for supplying an example that made it a lot easier (Excel did the translation).

    I will order myself a really cruel punishment now!

    I am very grateful for your kind help, please forgive me my error.


    Re: Date conversion

    Thanks for your effort but your solution only works in january every year. I am going for a more complete solution. Thanks all the same :) Mats


    I am trying to salvage some statistics (we are changing database) in the old database an ordinary date isn’t used instead 2006002 is used for the date 2006-01-02. 2006003 for 2006-01-03. This is some kind of "special" format year and day of that year. I have data from 1997-01-01 until today. 2006-04-07 is 2006097.

    The problem in short: Make 2006002 to 2006-01-02. 2006097 to 2006-04-07.

    The only solution I have is to make some lookup table from 1997 until today.

    But maybe some formula genius has some better solution?

    Any help will be greatly appreciated TIA /Mats

    Re: Automatically generate custom views/reports based on a database

    Yes Roy I agree with you BUT I find that when trying to use pivottables with accounting etc. a very common display of columns like:

    This year - Last year - Diff +/- - Diff%

    Is this possible to do? I only manage to get

    (See my file for exakt display)

    This Year Last year
    Diff %

    Wich I think is is a poor display. I always strive to put the same kind of values in the same column. I think the database functions give you more controll of display.

    I haven't been using pivottables a lot but are now commited to try to master them. Any help/input much appreciated. /Mats

    Re: Automatically generate custom views/reports based on a database

    Ok pivot tables are great but sometimes they can be a little hard to get the way you want them. The reports sometimes don't get "businesslike".

    I am working on something similar as you. One datasheet, one Querysheet using Excel database functions and then the reportsheet (the formated report using the query.

    Check out…

    It´s an XLent article.

    I havent used these functions before, they are a little hard to grasp but very powerfull.

    Best of luck to you!


    Thanks Dennis!

    You are THE MAN! Thanks for your kind help! It works just the way I want it.

    I have serched all over for information about this but found none. Is there more "international" oddities in VBA?

    This is a part of a project where I use SQL Tester to read sales statistics from an Sybase SQL database and make a textfile for uploading to our central database.

    SQL Tester works perfectly, I´ll swear I never gonna use M$ Query again.

    Tack så mycket! (Thank you very much in swedish)


    This past week without the forum have been awful!

    Every day go to the forum is it back up? NO!

    Mindless surfing of the webb for some "food for thought" but finding none.

    Thank God the forum is back up and it looks good too!

    Happy regards to everone,


    Thanks Derk, I am sorry if I explained the problem poorly, but the filename formating is ok. It's the formatting inside the file that gets messed up when using the VBA solution instead of the the menu/Macro recorder version.

    When using Sub SaveFileAs() a line inside the file looks like this.
    1 5/14/2004 10:26 1 91-0010309-8

    When using Sub Makro2() the same line looks like this.
    1 2004-05-14 10:26 1 91-0010309-8

    Could it be something with VBA formating and my own Swedish formating options??

    Again, Derk thanks for your effort!



    I am learning VBA and have a Q for the forum. When using this code

    Sub SaveFileAs()
    Dim stFpath As String
    Dim stFname As String
    'Folder to store file
    stFpath = "C:\JBStat\"
    'Name of workbook
    stFname = Range("B1").Value & ".txt"
    ActiveWorkbook.SaveAs stFpath & stFname, FileFormat:=xlText, CreateBackup:=False
    End Sub

    Really simple as you can see...I get a different formating inside the file (the dates in column two gets 5/14/2004 instead of (the way I want) 2004-05-14 as I get when I use the macro recorder and get this code.

    Sub Makro2()

    ActiveWorkbook.SaveAs Filename:="C:\JBStat\20040514.txt", FileFormat:= _
    xlText, CreateBackup:=False
    End Sub

    Examples of the outcome of the different macros.

    Sub SaveFileAs() 1 5/14/2004 10:26 1 91-0010309-8

    Sub Makro2()1 2004-05-14 10:26 1 91-0010309-8

    BTW I use XP and Swedish version of XL 2003.

    Any formatting experts out there? TIA :yes:

    Have a great weekend all! /Mats


    I just want to add something I find useful when dealing with time issues. Change to the Macintosh date/time system in tools-alternative-Calculation. This enables you to have negative time values, which can be very convenient.

    /Mats Carlsson

    Hi Dennis!

    You have already earned your dinner! I promise you, you´ll be treated to a meal at a restuarant called Little Hell next time you are in Halmstad :baddevil: It´s a place for XL fanatics, very hot!

    But on todays services you have earned coffee at my bookstore as well!


    PS You being from the north can´t you bring some Skogsstjärnan with you?

    Thanks Richie and Dennis for your feedback!

    I will go trough with your suggestions and post it back to the board.

    As always, you´ll always have free coffee and cokies on me if you come to the lovely town of Halmstad, Sweden!

    :beergrin: Mats

    Hello everyone!

    I have made my first Addin. It calculates a price depending on the supplier price. This is my first bit of code that I have done myself and not pasted...:-). If someone more experiénced programmer could give me some feedback on this I would be very thankfull. Dont hold back! I´m a big boy I can take it!

    TIA Mats

    Function PrisI(Fpris)
    ' Net price rounded
    If Fpris < 20 Then fprisav = WorksheetFunction.Round(Fpris / 0.5, 0) * 0.5
    ElseIf Fpris < 50 Then
    fprisav = WorksheetFunction.Round(Fpris, 0)
    ElseIf Fpris < 100 Then
    fprisav = WorksheetFunction.Round(Fpris / 2, 0) * 2
    ElseIf Fpris < 200 Then
    fprisav = WorksheetFunction.Round(Fpris / 5, 0) * 5
    ElseIf Fpris >= 200 Then
    fprisav = WorksheetFunction.Round(Fpris / 10, 0) * 10
    End If
    ' The rounded price is used to calculate consumer price
    If fprisav <= 110 Then
    capris = fprisav * 2.03
    ElseIf Fpris <= 170 Then
    capris = 223.3 + (fprisav - 110) * 1.91
    ElseIf Fpris &gt; 170.01 Then
    capris = 223.3 + 114.58 + (fprisav - 170.01) * 1.81
    End If
    ' VAT is added vat 6%
    caprism = capris * 1.06
    ' Price incl VAT is rounded upwards to even krona.
    baspris = WorksheetFunction.RoundUp(caprism, 0)
    PrisI = baspris
    ' Baseprice=capriset is change to the I-scale
    If baspris < 8.49 Then
    PrisI = baspris + 3
    ElseIf baspris < 30.99 Then
    PrisI = baspris + 4
    ElseIf baspris < 53.99 Then
    PrisI = baspris + 5
    ElseIf baspris < 88.99 Then
    PrisI = baspris + 7
    ElseIf baspris >= 89 Then
    PrisI = baspris + 12
    End If

    End Function

    Hi Jack!

    Thanks for your interest!

    Yes I am setting a criteria, but not on that field. Today I tried a weird way of doing this. In MS Query I marked all posts copied them then went into Xl and did a paste-values and the whole number is there, but unfourtunatly a whole lot more difficult to update.



    I am trying to connect my Sybase SQL Anywhere with XL. The info looks ok in Query but when its dumped into XL some info in some fields are missing, for instance the first 5 positions of a number is shown but not the remaining 5 (its an ISBN) is cut out. Anyone got a clue?

    TIA /Mats:flower:

    PS. Is it worth the time and effort to learn MS QUERY or should one try SQL*XL?


    I would like to lookup a value from a database like this.
    Date Sum
    2002-04-24 53500

    I want to base this lookup on a date and then lookup the value from one year ago.
    Sofar I have come up with
    Where Fsg is the name of the database and 2 the column index. But it doesnt work dont know why.....

    Anyone got a hint? TIA Mats:flower: