Free Microsoft Excel 2013 Quick Reference

Date Uploaded Shows Incorrect Date

Hey, I'm using a piece of code that I modified from this forum to upload data from an excel spreadsheet to an SQL 2000 Server. Here's the code:


	VB:
	
 ADODB.connection 
Dim Cn2 As ADODB.connection 
Dim ServerName As String 
Dim DatabaseName As String 
Dim TableName As String 
Dim UserID As String 
Dim Password As String 
Dim rs As ADODB.Recordset 
Dim RowCounter As Long 
Dim ColCounter As Integer 
Dim NoOfFields As Integer 
Dim StartRow As Long 
Dim EndRow As Long 
Set rs = New ADODB.Recordset 
 
 
ServerName = "MetricPlay" ' Enter your server name here
DatabaseName = "Optus" ' Enter your  database name here
TableName = "Landlines_0307_OrigData" ' Enter your Table name here
UserID = "UserID" ' Enter your user ID here
 ' (Leave ID and Password blank if using windows Authentification")
Password = "password" ' Enter your password here
NoOfFields = 15 ' Enter number of fields to update
StartRow = 2 ' Enter row in sheet to start reading  records
 
 '  CHANGES
On Error Goto ErrHandler: 
 
Dim shtSheetToWork As Worksheet 
Set shtSheetToWork = ActiveWorkbook.Worksheets(1) 
 'This code will update all rows, if you dont want to insert the whole spreadsheet into the databse, edit this.
EndRow = Cells(Rows.Count, 3).End(xlUp).row 
 '********
 
Set Cn = New ADODB.connection 
Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _ 
";Uid=" & UserID & ";Pwd=" & Password & ";" 
 
rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic 
 
 'If cnn.state = adStateOpen Then
For RowCounter = StartRow To EndRow 
    rs.AddNew 
    For ColCounter = 1 To NoOfFields 
        rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter) 
    Next ColCounter 
Next RowCounter 
rs.UpdateBatch 
 
 ' Tidy up
rs.Close 
Set rs = Nothing 
Cn.Close 
Set Cn = Nothing 
MsgBox "Landline Data Succesfully sent to the SQL Server." 
 'Else
MsgBox "There seems to be a problem with the SQL connection. Please ensure that the macro variables are correct and that the server MetricPlay is registered on your system" 
 'End If
 
Exit Sub 
 
ErrHandler: 
If Err.Number = -2147467259 Then 
    MsgBox "Cannot Connect to the SQL Server. Please ensure that the settings in the Macro setup are correct, including Server Name, Database Name and that your login has sufficient rights to access the server. **Integrated Authentication**" 
Else 
    MsgBox (Err.Number & " " & Err.Description) 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I had to comment out the If statement because whenever it hit that line I got a 424 Object Required error message, but without it it works fine, this is more a side issue that I can live without, the main problem is that like usual, excel manages to corrupt my Australian dates into a pseudo-US format. 1/3/2008 turns into 3/1/2008 but anything past the 12/3/2008 stays in the correct format.

Is there any suggestions on how I would correct these errors?


Post your answer or comment

comments powered by Disqus
Hmm, apparently I cannot attach the document. I'm trying to upload a .zipfile (because the document weighs in at 3,8 MB), but keep getting a database error. Smaller documents upload fine. Ah well, I'll try again later.

Greetings

First time poster and generally new to Excel.

I have attached an Excel 2007 document which I hope you can help me with. The sheet password is "copyright". Also, the document was not made by me, which is why I need your expert help.

Problem 1: When entering a date in sheet "1" it shows up as ###### even though the correct date is displayed in the input line. How come? Also, the data represented by this date should show up in sheet "Aktuel form" (it's a Danish document, sorry) corresponding to the month and year, but as you can see only years 2004-2008 are available. How do I update them to include 2009-2012? I hope this makes any sense at all.

Problem 2: The document is very large and contains alot of information that I don't need. How do I delete these sheets from the document?

Any help greatly appreciated as I've come to the end of the road of my very basic Excel-skills.

Sincerely,

/Zinne

What's the formula to format my date and show the month abbreviation:

1/2/2008 = Jan
3/15/2008 = Mar

Hi,
After installing SP1 for XL2003 subtotals show incorrect results. It works
fine on XL2003 without service pack

Excel 2003, SP1

I am copying the date column from one worksheet to another within a
workbook. If I paste or paste special values, the new date row shows a 5
digit number(36069), but the date in the formula bar shows MM/DD/YYYY
(12/18/2006) which is the date format I am copying. I have formatted the
receiving column to date before and after the paste - no change. The Tools,
Options, Calculation, Date System is unchecked in both sheets.

TIA,
CaroleO

I have one workbook with two worksheets. I am trying to figure out how I can make sheet 2,"which is a Pivot Table" not show dates under column titled, "Contact Due Date" which is being pulled from Sheet 1. I am going to use this column as a Query where dates will be picked but I do not want the dates to show on the Pivot Table column. Any ideas?

Hi

I’m trying to resolve this issue and would appreciate some help.

Scenario
I have a tracking sheet that tracks development of work to be completed by individuals.
I have a due date column that shows when the work should be completed.
Once the work has been completed, the user enters his completion date.

I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.

Problem
When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.

How can I eliminate this being shown as it throws out my average development day calculation?

Would appreciate some input.

Regards

Ather

I am pasting data from Internet Explorer into Excel and some of the data cosist of prices on bonds.

These are priced in 64ths. So, when I paste the data, the fractions show as years if they look like this on the Web page: 10/64, 8/64, etc. (12 or less shows incorrectly as dates in 1964).

The larger fractions such as 33/64, show correclty.

The smaller values will just never show like the larger ones, and it makes a big mess for me.

Can anyone suggest a way to correct this?

Thanks

Would anyone be kind enough to tell me how I can have the latest date a file was saved showing on the worksheet? For example, I open a worksheet that was last saved on Sept 22nd, and after updating it today, I save it and want the date to show today's date..? I'm pretty sure I've seen it in some files in my working life, so would appreciate advice on this!

Cheers, BD

OK folks, I have a bit of a problem:

I have a spreadsheet, which extracts data from a csv file if the submission date is between a start date and an end date.

Now let's say the csv file contains a data item with a submission date of 6 September 2008. Now if I open the csv file manually, the formatting is correct (UK style - dd/mm/yyyy hh:mm) - I can prove this by adding e.g. 1 to the date and verifying that it now equals 7 September.

However, when my VB code opens the CSV file, it opens with the date formatting still correct (i.e. UK style) - but it now takes the date to be 9 June 2008 - again can be verified by adding one to the date and getting 10 June 2008.

I have come across UK/US date formatting problems in the past, and usually "Cdate" solves the problem. However here, this doesn't solve the problem, because "Cdate" will only work if the raw data is correct (i.e. it is just a us/uk formatting problem). The problem is that when VB opens the CSV file, it is actually changing the data - rather than the formatting.

If anyone wants to have a look at this, I am calling the csv file with a line of code:

Code:
I have uploaded the CSV file here, so if you can give it a try:
https://www.yousendit.com/download/b...MGszeUtGa1E9PQ

Any ideas?

The only solution would be for me to manually open the report, format the column as numbers, and then convert to date, save the csv and then call it with VB - but this is not very elegant - and a pain in the neck.

Thanks.
Gopes

I’m having some issues with a formula that was correctly working for me last month…but for some reason is now showing incorrect info. The purpose of this formula is to determine the business hours between two MM/DD/YY HH:MM AM/PM formatted dates. Rather than not counting weekends, I need to not count Sunday and Monday.

=IF(MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,Data!$A$3:$A$32)+(WEEKDAY(B1)=7)-1)*3/8))

A = date #1
B = date #2
Data! = range of holidays to exclude

If A=1/31/08 21:00 and B=2/2/08 9:00, then the business hours (8-5) between should be 10…but this formula is showing that it’s only 6 hours.

Any idea what I’m doing wrong?

Thanks…

How can I make a cell show the current date automatically?

Like today is 12-5-07...I would like to open up my workbook and in cell B1, I would like to have the date automatically show up.

How, or can I, do that?

Hi,

Can you help?????

When I insert 30:00 hrs it is showing as 06:00, when I check the cell
it actually showing as 01/01/1900 06:00. I have formatted the cell
to show hh:mm but still no luck.

Thanks in advance.

Hi All,

I have created a spreadsheet for tracking my attempts to get a new job. In
it are columns for actions such as email and phone calls, and a final column
for the most recent action date. The idea is that this last column acts as a
trigger for me not to let any application go without attention for too long.

Is there any way I can make this last column show the most recent date from
the other ones?

Thanks,
P.

Saul,

I've come in on this discussion a bit late, but I had a similar - possibly
related - problem (when importing csv data with dd/mm/yyyy dates) a while
back, and found that it's a known problem with Excel 2003.

Microsoft Knowledgebase article 911750 - "The format of the dates is
incorrect when you use a VBA macro to convert a CSV text file in Excel
2003" - discusses a hotfix that's available to fix it, and also a code
modification which you can use.

I decided to use the code modification, rather than the hotfix, since it
(the hotfix) is a bit old and there have been some security updates since
it's issue and I couldn't be bothered messing around finding out what
updates I would need to reapply.

My knowledge on VBA and associated matters is extremely limited, but
assuming you're using XL2003 I suspect that what may be happening is that
when you open your csv file with VBA the dates which still appear OK are
actually imported as text, and then when you save them to a csv they show
incorrectly. Pure conjecture, I know, but it may be worthwhile using the
code modification suggested by MS when importing the data, and then see if
the correct format is maintained when you resave it.

Another article - "Converting date formats when the date isn't a date!" -
from http://www.fontstuff.com/casebook/casebook02.htm, may throw some more
light on your problem.

Just a thought. Hope it helps. :-)

Regards,

John

----- Original Message -----
From: "brawlsadford" >
Newsgroups: microsoft.public.excel.misc
Sent: Wednesday, May 16, 2007 4:41 PM
Subject: date format changes when I save to CSV via a macro

Hi Dave,

thanks for your response - I appreciate the help on this one!

Whether I open the new CSV file in Notepad or Excel, the entry reads:
9/14/2006 0:00 (in Excel the cell format upon opening is "General" - note
the change in hour format too)

The same entry in the master CSV (in both Notepad and Excel) looks like
this:
14/09/2006 00:00:00 (in Excel the cell format upon opening is "Custom -
dd/mm/yyyy hh:mm" )

When the data is pasted across into the macro workbook, the U.K. formatting
is preserved. After saving as CSV (automatically via the macro), the
formatting in the macro Workbook (i.e. the xlsm file) is still U.K. - if I
then save to CSV manually, the formatting is still dd/mm/yyyy hh:mm

It's only in the macro-saved version that this transposition to mm/dd/yyyy
h:mm is present.

Am I missing something?

Thanks Again,

Saul

"Dave Peterson" wrote:

> How did you verify that the dates changed?
>
> Did you reopen the CSV file in Excel or in Notepad?
>
> If you used excel, try using Notepad.
>
> brawlsadford wrote:
> >
> > I'm using a macro to extract rows of data from a large, master CSV
> > file -
> > breaking it down into chunks and re-saving it as smaller CSV files.
> >
> > One of the columns in the master CSV file contains date and time data in
> > the
> > format "dd/mm/yyyy hh:mm"
> >
> > The macro pastes this data into the workbook fine, but when the macro
> > saves
> > the sheet:
> > ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".csv",
> > FileFormat:=xlCSV, CreateBackup:=False
> >
> > ... all the dates have been transposed into mm/dd/yyyy!
> >
> > This doesn't happen when I save the sheet manually (Office button/Save
> > As...
> > CSV)
> >
> > My region settings are all set to U.K. - what's going on?
> >
> > Thanks, in advance, for your help,
> >
> > Saul

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy

I have an on-going spread sheet for PO's each mo a new sheet is created. In
October the columns that should be dates shows in the toolbar as a date - but
on the sheet it shows as a number (10/01/2008 shows as 39722). The cells are
formatted as dates.

Thank You

I am plotting scatter charts in Excel 2003. I have a diagonal line on each
chart (to show the line x=y), which I put there by adding a "dummy" series -
(0,0), (1,1), (2,2) etc. to give a straight line. However, on some of my
charts this shows up as not one but two straight lines - one showing the
correct x=y line and another one which also goes through zero but has a
different slope. I can't work out why this happens or how to fix it. If I
format the data series to show data markers, all of them lie on the correct
line and none on the incorrect line. I have made sure I haven't inadvertantly
added an extra series to my chart - the incorrect and correct diagonal lines
are definitely part of the same data series. This problem happens on only a
couple of my charts (the ones with the lowest values), even though I have
several other charts linked to the same "dummy" series.
I hope this makes sense. Has anyone got any ideas on how to fix this?

much like this:

http://www.ozgrid.com/forum/showthre...goto=newpost**

But I need it to show the 12 earliest dates, accompanied by information in the same row... (see attatchment)

The example has them sorted, but this will not be the case as there are literally hundreds of dates changing in the run of a day...so some of the earliest dates may be in the middle or end (completely random).

Hello all, Im really new to VBA and hoping somebody will be able to help me.

In my excel sheet:On column A I've Dates (4/1/2000 ,etc) and column B to I I've a list of values of differen Indices (KLSE, Hang Seng Index, etc)
Basically I need to create a UserForm to allow my users to select a range of dates they want and the corresponding Indices Value

Ive created a UserForm

VBA.jpg

But Im not so sure What codes I should type.
Ive this so far:

	VB:
	
 cmdOK_Click() 
    BeginningPeriod = txtPeriod1.Value 
     
     
     
     
    nRow = numRow(BeginningPeriod) 
    nCol = lstIndices.ListIndex + 1 
     
     
     
    Indice = lstIndices.Value 
     
    If nRow  0 Then 
        mark = Range("A1").Offset(nRow, nCol).Value 
        MsgBox "Your Mark for " & Indice & " is " & mark 
    Else: MsgBox "Your ID is not on the list" 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But this only shows the corresponding Beginning Period's value. How do I get a range (BegnningPeriod - EndPeriod)? Also I need to import the data to a new worksheet
I'm sorry if this post is really lengthy But I'm really new and Ive been trying for the past 7 hours Please Help?

Thanks a lot

Hi All

I have been trying to figure out why when I group the pivot table by date, it changes the date format from Australian to Korean for some unknown reason.

Ungrouped

Grouped

I have checked all the regional settings on my PC and they are all set correctly (to Australian), The date format of the sheet also looks correct.

I have attached a zipped sample of the worksheet I am using (Sheet in question is Australia and the pivot table gets its data from the visit entries sheet)

sample.zip
(correct zip added now)

I am currently using Excel 2002 if that makes a difference.

Thanks in advance for any suggestions on how I can get the grouped dates to show using the Australian format.

Rich

Hi all,
is there any way to show date automatically when i open my excel spreadsheet?
for instance, if i exported a file that has 100 rows and 5 columns and save
it as *.xls file. When i open the file it will show current date in a new
columns? is it possible?

I have as one of my fields in a pivot data set the date of a transaction.
The transactions are sales transactions. I need the pivot to show me the
last date (or two at most) that a particular type of sale happened. I have
my rows as basically acct name, Order Type (there are two main types), and I
need the last time a txn happened (which would involve a "Units" field to
have more than zero units, and therefore I am using this as my data) to show.
Descending sort effectively gives me the last date first, but I don't want
to see all the other sorts.

Thx for any help.

--
Boris

I have as one of my fields in a pivot data set the date of a transaction.
The transactions are sales transactions. I need the pivot to show me the
last date (or two at most) that a particular type of sale happened. I have
my rows as basically acct name, Order Type (there are two main types), and I
need the last time a txn happened (which would involve a "Units" field to
have more than zero units, and therefore I am using this as my data) to show.
Descending sort effectively gives me the last date first, but I don't want
to see all the other sorts.

Thx for any help.
--
Boris

Hi all,

I'm new to VBA so my i'm having alot of problems figuring out simple stuff.

Below is my script and when i run it, the dates turns out incorrect.

I noticed this only happens to dates that are before 13th of each month.

Example,

1st May 2008 ( 01/05/2008)

will turn up as

5th Jan 2008 ( 05/01/2008)

However when i manually open the file Todays trades.csv
The date looks just fine.

Is there some problem with using VBA to call up the file?


No luck finding an answer? You could always try Google.