Date format issue when submitting from a userform to a spreadsheet

Hi,

I have a userform that I've generated which routes dates onto a spreadsheet 
based on the users input.  I am having a bit of a frustrating time with the 
dates, it would appear that in the process of moving the date from the 
userform to the spreadsheet some dates are switched/transposed.  I'll give an 
example.  If someone enters 09/02/2004 on the userform excel seems to look at 
09 and assume it is a month and transposes the dates to 02/09/2004(this does 
not appear to be a US/UK format issues as I have already gone down that 
road).  However, if the date 13/09/2004 is entered into the userform no 
change occurs. the format remains integral.  It would appear that if the 
first "day" figure is less than 12 excel transposes the date but if that 
"day" figure is more than twelve it doesn't.  I am aware of a similar issue 
with merging dates to excel to word but have not yet come across this within 
excel itself.  Does anyone have a possible suggestion of a way around this?  

Many thanks
-- 
Andy T
0
AndyT (25)
11/3/2004 5:17:35 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
908 Views

Similar Articles

[PageSpeed] 47

Hi Andy,

> I have a userform that I've generated which routes dates onto a spreadsheet 
> based on the users input.  I am having a bit of a frustrating time with the 
> dates, it would appear that in the process of moving the date from the 
> userform to the spreadsheet some dates are switched/transposed.  I'll give an 
> example.  If someone enters 09/02/2004 on the userform excel seems to look at 
> 09 and assume it is a month and transposes the dates to 02/09/2004(this does 
> not appear to be a US/UK format issues as I have already gone down that 
> road).  However, if the date 13/09/2004 is entered into the userform no 
> change occurs. the format remains integral.  It would appear that if the 
> first "day" figure is less than 12 excel transposes the date but if that 
> "day" figure is more than twelve it doesn't.  I am aware of a similar issue 
> with merging dates to excel to word but have not yet come across this within 
> excel itself.  Does anyone have a possible suggestion of a way around this?

Sure. Instead of just assigning the text of the text box to the cell (letting 
Excel do any conversions), explicitly convert it yourself:

Instead of:
 Range("A1").Value = TextBox1.Text

Use:
 Range("A1").Value = CLng(TextBox1.Text)

For (much) more information about Excel's international issues, see 
http://www.oaltd.co.uk/ExcelProgRef/Ch22/

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk


0
11/3/2004 5:54:51 PM
Thanks Stephen,

I think this is the solution but I am struggling with the code I am using.  
Currently I am using two commands, one to determine the next available row on 
the spreadsheet and one to transfer the data, thus

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

and

Cells (NextRow ,1) = TextBox1.Text

If I change the =TextBox1.Text to =CLng(TextBox1.Text) I get a Runtime Error 
13. Forgive me but my knowledge here is scant, perhaps you could let me know 
where I am going wrong.

Many thanks

"Stephen Bullen" wrote:

> Hi Andy,
> 
> > I have a userform that I've generated which routes dates onto a spreadsheet 
> > based on the users input.  I am having a bit of a frustrating time with the 
> > dates, it would appear that in the process of moving the date from the 
> > userform to the spreadsheet some dates are switched/transposed.  I'll give an 
> > example.  If someone enters 09/02/2004 on the userform excel seems to look at 
> > 09 and assume it is a month and transposes the dates to 02/09/2004(this does 
> > not appear to be a US/UK format issues as I have already gone down that 
> > road).  However, if the date 13/09/2004 is entered into the userform no 
> > change occurs. the format remains integral.  It would appear that if the 
> > first "day" figure is less than 12 excel transposes the date but if that 
> > "day" figure is more than twelve it doesn't.  I am aware of a similar issue 
> > with merging dates to excel to word but have not yet come across this within 
> > excel itself.  Does anyone have a possible suggestion of a way around this?
> 
> Sure. Instead of just assigning the text of the text box to the cell (letting 
> Excel do any conversions), explicitly convert it yourself:
> 
> Instead of:
>  Range("A1").Value = TextBox1.Text
> 
> Use:
>  Range("A1").Value = CLng(TextBox1.Text)
> 
> For (much) more information about Excel's international issues, see 
> http://www.oaltd.co.uk/ExcelProgRef/Ch22/
> 
> Regards
> 
> Stephen Bullen
> Microsoft MVP - Excel
> www.oaltd.co.uk
> 
> 
> 
0
AndyT (25)
11/3/2004 11:47:01 PM
Hi Andy,

> Cells (NextRow ,1) = TextBox1.Text
> 
> If I change the =TextBox1.Text to =CLng(TextBox1.Text) I get a Runtime Error 
> 13. Forgive me but my knowledge here is scant, perhaps you could let me know 
> where I am going wrong.

Oops!  Sorry, you need a CDate() in the middle there too, to convert the text 
box to a Date number:

If IsDate(TextBox1.Text) Then
  Cells (NextRow ,1) = Clng(CDate(TextBox1.Text))
End If

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk


0
11/5/2004 8:55:55 AM
Many, many thanks Stephen,

You have been a great help.

Kind Regards

"Stephen Bullen" wrote:

> Hi Andy,
> 
> > Cells (NextRow ,1) = TextBox1.Text
> > 
> > If I change the =TextBox1.Text to =CLng(TextBox1.Text) I get a Runtime Error 
> > 13. Forgive me but my knowledge here is scant, perhaps you could let me know 
> > where I am going wrong.
> 
> Oops!  Sorry, you need a CDate() in the middle there too, to convert the text 
> box to a Date number:
> 
> If IsDate(TextBox1.Text) Then
>   Cells (NextRow ,1) = Clng(CDate(TextBox1.Text))
> End If
> 
> Regards
> 
> Stephen Bullen
> Microsoft MVP - Excel
> www.oaltd.co.uk
> 
> 
> 
0
AndyT (25)
11/5/2004 5:08:03 PM
Reply:

Similar Artilces:

2000 File Export Issue
Running Outlook 2000 (9.0.0.2711) and trying to export a date range to Excel. I'm following the instructions, but all I get are the column headings -- no data. What am I doing wrong? ...

Conditional Formatting Equation Question
Greetings, I am trying to get a Conditional Format to do something if there is "NO" entry in A2. Any ideas as to what this formula would look like? Any help would be appreciated. TIA -Minitman =A2="NO" or, if by "NO" entry, you mean a blank cell: =A2="" HTH Jason Atlanta, GA >-----Original Message----- >Greetings, > >I am trying to get a Conditional Format to do something if there is >"NO" entry in A2. > >Any ideas as to what this formula would look like? > >Any help would be appreciated. > >TIA >...

Maintain formatting of sub project when inserted into master proje
I'm using Project 2007. I have a master project and have inserted some sub-projects into the master project. ('Link to project' was checked which is what I want.) I had applied formatting to the original sub-projects' gantt charts task bars e.g. sub-project1 task bars were green, sub-project2 task bars were red etc. I found that this formatting was lost after inserting the sub-projects into the master. The sub-projects assumed the formatting settings of the master. Is there a way to maintain sub-project formatting? Thanks -- FionaDM In article <000A42E...

same date and set up on complete workbook
Is it possible to set the date only once for the complete workbook? I have 12 pages. Thank you. Ed, Not exactly sure what you mean. If you mean, insert it onto 12 different tabs/worksheets within a workbook and they're all in the same cell (e.g. A1), you can select cell A1 in Sheet 1, and then shift click on the final tab/worksheet. This will select cell A1 in all tabs. Press Ctrl ; to insert the date and press Ctrl Enter to enter it onto all 12 cells spread across the sheets. Clive -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's...

Sync Issues Outlook 2003 / Exchange 2000
We've got a number of remote users who up until recently have bene working fine with synchronising mailboxes at home, then suddenly all users end up doing a full sync taking ages instead of a normal. Is there a setting on the serve that could of changed? Paul ...

Sort date by day and not year
Need to sort birthdates by month and day; ignore year. Ex: Would like to sort like list below: 3/02/1997 3/15/2002 3/21/1996 Not like this: 3/21/1996 3/02/1997 3/15/2002 Please help and thank you for your help. Kathy Hi one way: - add an additional helper column (lets ay B) - insert the following formula in B1 (if column A stores your list): =MONTH(A1) - copy this formula for all rows - sort with this helper column -- Regards Frank Kabel Frankfurt, Germany Kathy wrote: > Need to sort birthdates by month and day; ignore year. > > Ex: > Would like to sort like list below: >...

Credit Card Expiration date error
We are getting an error message for credit cards with expiration dates greater than 2010. Has anyone else seen that? We are currently on RMS 1.2 using PC-Charge. It takes expiration dates through 2009. hi Rick, There is update for 1.2 to 1.3 and also in 1.3 there are patches available kindly download from customer sources this will resolve the issue "Rick@ASP" wrote: > We are getting an error message for credit cards with expiration dates > greater than 2010. Has anyone else seen that? We are currently on RMS 1.2 > using PC-Charge. It takes expiration dates thr...

Send message issue
Can SendMessage can be used to send a message from document to a window which is subclassed I am proceesing some work (function) in the document and after the work is finsihed I need to send a message that the connection is established to the control and control starts downloading the data from document. thanks Tom > Can SendMessage can be used to send a message from document to a window > which is subclassed Yes. You can use SendMessage from anywhere as long as you are sending it to a window. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com Hi Ajay, this is how I am sending it ...

LDAP Issue
Hello, We work with an Authentication Service which does LDAP Connections to 3 different Domains. The Server with the Authentication Service is in a separate Domain and Network zone. The Server is running Windows Server 2003 R2. Tha Authentication Service retrieves information from the 3 domains: DomainA: All DCs running Windows Server 2003 DomainB: All DCs running Windows Server 2003 DomainC: All DCs running Windows Server 2008 The LDAP Bind for Domain A and B works fine. But the Server is unable to make a LDAP Bind to the Windows 2008 DC. More Information: - The F...

emailing spreadsheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Every time I email a spreadsheet as an attachment the recipient cannot open it. I have tried copy and pasting it onto a word doc but apparrently it still doesn't open. What am I doin wrong It's hard to be sure because you don't indicate what *does* happen when the recipient tries to open it, whether these are Mac or Windows users, nor do you disclose any details pertaining to the email specs you or the recipients are using. You may be doing nothing wrong at all. It could be a problem with your ...

Calculate A Date Excluding Weekends And Holidays
I have a date and a series of tasks that need to be completed by a certain number of days from the intial date. The time to complete the tasks can not include weekends or holidays. Example: Initial date is 2/15/2006 I have 10 business days to complete a task All Help will be appreciated. Thank you. -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17623 View this thread: http://www.excelforum.com/showthread.php?threadid=510032 =WORKDAY(A1,10) Format as dat...

Security and encryption issues when trying to convert to 2007
I currently have a 2003 database set up with a workgroup and user end securities. I know these won't work in 2007, so I've already followed the suggestions through a few other posts and essentially gotten rid of my securities by changing all the profiles to admin with full access and rights. But my database is still encrypted and I receive an error message when I try to convert it. I could just convert the version of the database I have before the securities were added, but I've made a lot of changes since then and don't want to have to redo all those hours of wo...

How-To: Last change date on row
When I set up a database I always include a last change date that auto updates every time any field in the record changes. Does anyone have a suggestion for how to achieve a similar function in a spreadsheet. i.e. put the current date/time in a field in the row whenever anything in the row changes? -- GGG ------------------------------------------------------------------------ GGG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29220 View this thread: http://www.excelforum.com/showthread.php?threadid=489447 GGG, For example, to put the last change date in...

save formats of percentage when copying a chart
How do I keep the percentage format of data on a chart when I copy/move the chart to another spreadsheet that does not have the original data in it? ...

conditional format #3
Fra: "Niels B�ge Egstrand" <nbenospam@djoef.dk> Emne: Re: Condition Formatting! Dato: 2. november 2004 11:55 is it possibel to opperate with 9 different collors in conditional format ? No, you get up to 3 formats under conditional formatting (plus the normal format). If you need more formats, you could use some event macro. "Niels B�ge Egstrand" wrote: > > Fra: "Niels B�ge Egstrand" <nbenospam@djoef.dk> > Emne: Re: Condition Formatting! > Dato: 2. november 2004 11:55 > > is it possibel to opperate with 9 different collors in...

Outlook 2003 PST file issues
I created a backup.pst form Outlook 2003. I also copied the outlook.pst file onto a cd. I had to format my HD and reinstall Windows XP and Office 2003. When I try to open either of the two PST files I get an error message "The file C:\path info\backup.pst is not a personal folder file." Can anyone please help me. All of my important email data is in there. I followed the instructions on the Microsoft site for backing up the file, not it won't open. Did you remove the read only attribute these files acquired from being on a CD? -- Russ Valentine [MVP-Outlook] "Brad&...

How do I set If Statement to set Priorities, off of dates due?
I am trying to set up If Statements to read dates for several projects and from those dates set priority levels of A, B, C, etc... Can anyone offer assistance? Try something like: =IF(B1=DATE(2005,1,1),"A",IF(B1=DATE(2005,1,2),"B","C")) ---- Regards, John Mansfield http://www.pdbook.com "K Svoboda" wrote: > I am trying to set up If Statements to read dates for several projects and > from those dates set priority levels of A, B, C, etc... Can anyone offer > assistance? One way: assuming the due date in A1: =IF((A1-TODAY())<=0,&q...

display a number that is decimal degrees in Deg:min:sec format
How can I create a format to display a number that is in decimal degrees, in Degree:minute:second? See http://cpearson.com/excel/latlong.htm In article <DD958DEA-4DE1-4C80-80D5-D959F720E452@microsoft.com>, "Sterling" <Sterling@discussions.microsoft.com> wrote: > How can I create a format to display a number that is in decimal degrees, in > Degree:minute:second? ...

format cell to change color when copied
I do alot of cut and pasting. Is there a code that will change the color of a cell if I right click to copy it, so I can track what I have worked on? Thanks, Tawnee -- tawnee jamison ------------------------------------------------------------------------ tawnee jamison's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16137 View this thread: http://www.excelforum.com/showthread.php?threadid=346063 Tawnee Can be done, but you'll soon end up not knowing which was which. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) ...

How do I get a 2 digit date?
I have a control on a form which is a Date/Time control called "txtcd1retd", its fomat is dd/mm/yy There is another control called "txtYear" with it control source =Year([txtcd1retd]). If "txtcd1retd" is 01/02/08 I want txtYear to be 08 but I get 2008. How can I just get 08? Thanks Tony Try: =Format([txtcd1retd], "yy") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tony Williams" <tw@invalid.com> wro...

Bullet Point Animation Fade in issue
Hi, I have set up my bullet points to fade in (1st one 'on previous event' remaining points on same page 'on click') This all works fine on my first page - none of the bullet points are present at all (except the automatic first one) and they appear from nothing to black as they should one by one when I click. Slide two and the remaining slides however have all of the bullet points and sentences present on screen in light grey. When I click they then solidify into black (this solidify to black part is obviously correct). How do I prevent the words from appe...

Locking formatting
Does anyone know how to lock the formatting, validation rules and conditional formatting in a cell without locking the data? I have a data entry template and everything works fine unless a user copies an unlocked cell and pastes it into another unlocked cell, then the formatting and validation rules are taken along as well. I can stop this for myself by using Paste Values, but I can not see how to only allow the pasting of values for other users of the spreadsheet. I have searched everywhere in the Help files and have the feeling that this is just not possible using standard Excel func...

Viewing Spreadsheet Data
When viewing the spreadsheet data on a particular excel file, using a network. The spreadsheet file shows a blank sheet until viewed in print preview. The data is visible on one computer but not the other. The data prints OK but is not visible! Any suggestions? ...

how to get several payment dates
Hi all! i can't think of how to do this, mabey someone can help me. i have a tbl employees (not our employees but employees from a company thet we sold to) i have the $ amount sold and the # of payments they have to do (credit) this payments vary in that some are every 7 days others every 14, 15 or 30 days, so say an employee bought $100 in Feb-02-2007 and has a credit of 8 weekly payments: EmpID = 1 EmpAmount = $100 EmpCredit = 8 EmpDays = 7 EmpDate = 02/02/2007 now i need to calculate very date we expect payment from them: EmpID EmpNextPay1 EmpNextPay2 EmpNextPay3 EmpNextPa...

How do I display results of a summation in the following format?
Formula: ="Total = "&round(sum(a1:a12),2)" Assuming the result is 123456.78. Can the final results be displayed in the cell with number format (accounting) as... Total = 123,456.78 ? Try the below. ="Total = "& TEXT(SUM(A1:A12),"#,##.00") If this post helps click Yes --------------- Jacob Skaria "Nh2" wrote: > Formula: ="Total = "&round(sum(a1:a12),2)" > Assuming the result is 123456.78. > > Can the final results be displayed in the cell with number format > (accounting) as......