Vba Date$

I have to use the Date$, and this seems to be swapping the day and month:

The system date is 04/06/2010 (dd/mm/yy), and the regional setting are for 
the UK

when I try   Dstr = Format(Date$, "dd/mm/yy"), I get 06/04/2010, and when I 
break it down into individual elements, ie

  Y = Year(Date$)
  M = Month(Date$)
  D = Day(Date$)

I get M = 4 & D = 6! 

Please help!

-- 
Eric
0
Utf
6/4/2010 11:27:59 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1205 Views

Similar Articles

[PageSpeed] 30

Try using

DStr = Format(Date, "dd/mm/yy")


-- 

HTH

Bob

"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:A93C4128-4E48-46B3-A59B-33FED5A4D422@microsoft.com...
>I have to use the Date$, and this seems to be swapping the day and month:
>
> The system date is 04/06/2010 (dd/mm/yy), and the regional setting are for
> the UK
>
> when I try   Dstr = Format(Date$, "dd/mm/yy"), I get 06/04/2010, and when 
> I
> break it down into individual elements, ie
>
>  Y = Year(Date$)
>  M = Month(Date$)
>  D = Day(Date$)
>
> I get M = 4 & D = 6!
>
> Please help!
>
> -- 
> Eric 


0
Bob
6/4/2010 11:38:04 AM
Reply:

Similar Artilces:

Date compatibility error
Hi All, I was hoping I could get some help with my problem, as i have trie everything i know. I have this spreadsheet (attached) and when i type the date in m formula doesnt want to find the corresponding data. But when the date are copied from the data table and pasted in (same dates) it finds th data fine. I have tried formatting the dates in the same way but with no change. NB: If possible i would like to keep the formatting on the data tabl without changing as this is a direct data import from another program If this is the only possibility, let me know and I will try and fix i in a ...

Count values either vba or formula
Sample of what I am trying to achieve: Worksheet1: (column A, B, C, D, E) - source data id,primary region, secondary region, tertiary region, status 111,americas,new york, NA, increase 111,americas,canada,NA, increase 111,americas,mexico,LATAM,increase 111,americas,peru,LATAM,decrease 112,europe,france,WEST,increase 112,europe,spain,WEST,decrease 112,europe,uk,WEST,decrease 112,europe,portugal,WEST,decrease etc.... Worksheet2: (contains summary sheet) - summarize data 111,americas,NA, increase, 2 <----- count instances found from worksheet1 111,americas,LATAM,increase, 1 111,americas,LA...

post a thread via VBA code
Hi there I beleive tyhat there is a userform with vba code to post a thread ? any idea ? ...

Damsel in distress needs VBA help!!!
Hi, I have 45 customer service reps weekly schedules in an excel spreadsheet. They all are on one of 4 teams and are listed alphabetically as so: Doe,John start time break 1 lunch break 2 Monday 8:00 10:15 1:30 3:15 Tuesday Weds etc.. I need them to be sorted by team. I think a macro for each team would be the easiest way to do this. I need code that would go through the alphabetical list and search for an agents name and delte that row and the 10 rows below it. I have no idea where to begin or even how to be able to set...

changing date format
hi, I'm trying over and over to change the date format cells,and it just doesnt allowed me,allow is not the right word - it just does nothing,or it changes only a few of the cells to the wanted format. I tried to change other date format cells on other file it went just fine,so why is it happening on the first file i was talking about ? what should i do ? thank you in advance,hummiz -- hummiz ------------------------------------------------------------------------ hummiz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23877 View this thread: http://www...

Formatting dates by year
I would like to know if there is a way to format dates so that they are formatted by year only. For example, if I click on Format -> Cells, there are a bunch of options, but none of them list to show the year only, just mm/dd/yy or mm/yy, etc. I am trying to create a chart so that only the year shows up. Thanks. HI try clicking format cells custom then in the 'type' box, enter yyy -- Message posted from http://www.ExcelForum.com Hi, I have tried this, but what it does is give one specific year (ie. 1905) for all the years I have entered. I want to just keep my values. ...

Formatting text and date together
How can I format a cell to hold text ("Last update:") and a date whic is obtained from another cell? The Formula reads something like +"Las update: "&f2. The date comes out as its numerical value. Is it possible. Thank -- Brisbane Ro ----------------------------------------------------------------------- Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2509 View this thread: http://www.excelforum.com/showthread.php?threadid=49227 Use the text function to format the numeric component to suit your needs, along the lines of ...

Date and Time Table Modified
I have 4 forms used by managers to enter data. When they enter data, the data is saved in tables. I would like to include a date and time stamp so that I know when they entered each piece of new data. Do I need to have a field in my table to collect the date and time? How do I collect that in my form? Thanks!! Yes, you need to add a field to your table. In the form's BeforeUpdate event, put code to update that field. Assuming that you named the field LastUpdated and that you included the field in the form's RecordSource, the code would look something like: Private Sub Form_Bef...

in VBA
Using Office 2003 I would like to reset any custom filter settings from all columns. The following resets Column 1 only: Selection.AutoFilter Field:=1 What is the VBA code to reset Columns 1-17? TIA Dennis Dennis, Selection.AutoFilter You could have found that out by yourself if you had just used Macro Record (as I did). -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dennis" <dmburgess@removespamameritech.net> wrote in message news:961i10teu4q5r2t6rh38bmen6lski9r6f6@...

Excel VBA
I can't seem to find the 'Draw" menu in Excel VBA... Am I correct in assuming that there is none? And that the Frame is the only way to group stuff on a Form?? thanks The frame is typical, but you can group some controls by using the GroupName. If you want lines on the userform, I think lots of people use a label that's really skinny--or use a picture of a line and then use that picture. tmb wrote: > > I can't seem to find the 'Draw" menu in Excel VBA... > > Am I correct in assuming that there is none? > > And that the Frame is the onl...

Next Date given one date
At this time, I cannot use VBA. Here is my objective: For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates are: 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be...

Date and Number cell formats
I am using Excel 2003 and I am trying to create a very basic weight chart by date. Column one is the date and column two is the weight. I would like all of the dates to appears as 01/06/2010, 10/05/2010, 11/15/2010, etc. -- in other words, mm/dd/yyyy with 2 digits for the month, 2 digits for the day, and 4 digits for the year. Instead, all I get for the date is: 1 or 2 digits for the month, depending on whether the month is a 2-digit or 1-digit number; and the same for the day, 1 or 2 digits. I want them all to appear as mm/dd/yyyy. I would like the weights to appear as 211.6 , 212.0...

Business Portal Project Timesheet date header dissappear when scroll down
In Business Portal Project timesheet, when scroll down the cost category, the date header is dissappear from the page, is this a design fault ? For project will large no of cost category, it is very in-convenient as we did not know whether the hours being keyed into the correct date. Please advise. Thanks ...

AUTO DATE RECOGNITION
HOW CAN I STOP PART NUMBERS WHICH HAVE DASHES IN THEM BEING CONVERTED INTO DATES WHEN I DO A DATA IMPORT INTO EXCEL? Yell real LOUD. That does it for me. Seriously, please don't use all caps for subject or body of msg. It is considered shouting and rude. Try pre-formatting your destination range to text. -- Don Guillett SalesAid Software donaldb@281.com "TONY" <anonymous@discussions.microsoft.com> wrote in message news:AD32B08C-CD73-4585-B706-6F29B17894D0@microsoft.com... > HOW CAN I STOP PART NUMBERS WHICH HAVE DASHES IN THEM BEING CONVERTED INTO DATES WHEN I DO A ...

Outlook Archive NOT using Modified date
Is there anyway to force Archive to look at sent or received and not consider the modified date to archive items. We put in a new server and migrated all the mail to the new server so now the modified date on all emails has changed to that date. Now everything looks only a couple of months old even email from 2 and 3 years ago. I need to get archive to ignore the modified date. Any way to do this? No. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engine...

European vs american date notations
Hi, I have a strange problem in Excel. I want to enter a european date 20/09/2004 and convert it into dd/mmm/yyyy what should display 20 Sep 2004 BUT it does not it stays like 20/09/2004. WHen I rewrite the date in 09/20/2004 and convert it does show 20 Sep 2004. The cell properties is set to english date and internation setting is also english. Why cant I enter european dates into excel.. Excel uses your Control Panel date settings to help determine what kind of an entry you are making. While 20/09/2004 is a valid date under many other countries' settings it is not with U.S. s...

Workflow Call Assembly to add Date or Time
I cannot get a very simple workflow to work. All I want it to do is update a datetime field to add one month! Workflow monitor says it completed, but it has never worked... Has anyone ever been able to make this work? On 26 Mar, 18:54, "DubSport" <jamie.carmich...@cmgl.ca> wrote: > I cannot get a very simple workflow to work. All I want it to do is > update a datetime field to add one month! Workflow monitor says it > completed, but it has never worked... Has anyone ever been able to > make this work? Show your workflow rule OK here it is. its a manual rule, s...

Voucher payables document date 2018
A voucher (check) was posted with the Document Date of 12/17/2018. The invoice itself was dated 12/17/2008. We are trying to VOID this transaction but I get: "The void date cannot be before the document date" (meaning today of 1/16/2009 is before 12/17/2008) The Payables Transaction Posting Journal/Payables Management shows INV Voucher # with the doc date as 12/17/2008 but Check reconciliation cannot be completed because this check is out of the period. Please advise best method of fixing this error. Thank you Pam, Either go into the table and change the document date or c...

I fixed the date and time and the time zone. Now reply me please
Hi, I have some questions in outlook 2003, can anyone help me? 1-how can i in the appointment type a subject with special format (red, bold..) 2-whent a task is 100% completed, how can be deleted automatically. 3- I need to filter only the available time on a specific day. ex if i have 2 appointments today on a specific time, what i need to see is the available time for other new appointments. 4-If I have a lot of appointments on a specific date that are colored with labels and have a specific show time as (busy,tentative), when i go to print this calendar with the "calendar detai...

Date and Time ??
I had a question working about date and time it had 11 or 12 posts and now it's gone. Any Idea what happened to it? Sorry I found it. "Duane" wrote: > I had a question working about date and time it had 11 or 12 posts and now > it's gone. > > Any Idea what happened to it? "Duane" <Duane@discussions.microsoft.com> wrote in message news:9F0A6769-90FA-4E91-81AF-22F2B7549C32@microsoft.com... >I had a question working about date and time it had 11 or 12 posts and >now > it's gone. > > Any Idea what happen...

default date in Excel
How do you change the default date format in Excel. I am running Excel 2000 but we also have Excel 2002 and Excel 2003 in our office. Will Fleenor will@k2e.com Hi Will It's a Windows setting. Change it in the control panel. HTH. Best wishes Harald "Will Fleenor" <will@k2e.com> skrev i melding news:Of7bGQJXFHA.1148@tk2msftngp13.phx.gbl... > How do you change the default date format in Excel. > > I am running Excel 2000 but we also have Excel 2002 and Excel 2003 in our > office. > > Will Fleenor > will@k2e.com > > ...

Excel VBA
I have a workbook that has multiple worksheets and 2 of them are letters. I would like to autofill data into the letters by using the userform. I have been able to code it using 1 worksheet. Is it possible to use same userform for the different worksheets? Thank you! Very simple, but you need to be more specific if you want some aid. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "djshides" <djshides@discussions.microsoft.com> wrote in message news:51B36917-FC0E-4567-B7D7-4B37E8A365B1@microsoft.com... > I have a workbook t...

How to select combobx value with vba?
For an electrical part number quoting application developed in Excel 2000, I have a a userform (userform4) with a combobox (cboQpn) to hold the quoted part number, and another combobox (cboFormula) prepopulated with a named range (Partnum) The user types in a part number in cboQpn and then chooses the correct formula from cboFormula. Now in access I have a table (tblDetail) that holds previously quoted part numbers and their details--the formula used in the previous quote, for example. The formula information is in column 5 in tblDetail I want to emulate the manual selecting o...

VBA moving and adding data
I have this appliaction that spits out a format that is not usable for me, but with some minor changes it would be good to go. I just don't know how the code would work. here is what part of my file looks like: ColumnA Coulmn B Column C Column D FAULT F02 8 PILLAR 3018169.273 492458.0997 -9771.365343 3017502.798 493106.6833 -8696.365343 3016836.323 493755.2668 -7621.365343 3016169.848 494403.8503 -6546.365343 3015503.373 495052.4339 -5471.365343 Here is what I would like it to look like: ColumnA ...

VBA Function to explode string
Hi. I am wanting to know hbow I would go about getting the following 3 cells: Cells A1, A2, A3: :: 1-4,6,8 50 S1B1:: Into a form that looks more like this ::A1 A2 A3 1 8.33 S1B1 2 8.33 S1B1 3 8.33 S1B1 4 8.33 S1B1 6 8.33 S1B1 8 8.35 S1B1:: How it does the rounding (and on which one), it does not matter! Thanks, Tom -- tomjermy ------------------------------------------------------------------------ tomjermy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24666 View this thread: http://www.excelforum.com/showthread.php?threadid=382414 You haven't exp...