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.
:-)Mats
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
Hi
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+/-
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 http://www.ozgrid.com/FreeDown…nctionsWithValidation.zip
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!
/Mats
Thanks Dennis!
It works just the way I want it.
Thanks a million for your kind help!
I am getting there slowly but....
/Mats
Thanks Dennis,
It works but now I have to refresh data manually...Now I will try to make som code for that. I am learning, slow but steady....
Sometimes one get so tired of M$.
Thanks for your kind help, Dennis!
/Mats
I have made a template that makes a query to an external database. Every time I make a new sheet using this template I get the "This workbook uses a query do you want to update ....." . Does anyone how to turn this splash screen question off? I use XL2003.
TIA Mats
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)
Mats
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,
Mats
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!
/Mats
Hi!
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
:cool:
Hi,
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: http://www.lillahelfwetet.se. It´s a place for XL fanatics, very hot!
But on todays services you have earned coffee at my bookstore as well!
:nana::mad:
Mats
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 > 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
Hello!
Thanks Andy! It works!
:beergrin: /Mats
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.
/Mats
Hi!
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?
Hi!
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
=lookup(DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)),Fsg,2)
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:
