Date format issue when submitting from a userform to a spreadsheet
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 i...Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a
table that has both text and numbers. They are part numbers, for example
21BC124. I kept the field as text because of the text with in the numbers
and didn't figure that a numeric field would alow the text. In my part
numbers table it sorts correctly (first by number then by letter then by
number again), but in my reports and queries there are a few number that sort
in the wrong place. Like this...
I can't quite f...cell looses name after sorting
Can someone help me with the following problem in Excel 2000:
in a table I have attached serveral cells with unique cell names, the
values in these cells are used in other sheets.
the problem is that when I sort the table, the cell names stay in the
original rowposition; they are not sorted! while their values are. So
Cell names get different values, and other calculations on my other
sheets get messed up!
How can I make the cell names relative instead of absolute?
thankx in advance,
Message posted from http://www.ExcelForum.com/
"jimfx >" <<jimfx.109zcv@exc...Keyboard shortcut for current date and time
Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me
Ctrl+Shift+; inserts the current time with the date serial as 0 and
not the current date's date serial.
Presently I am adding the two (ie current date and time) to get the
current date and time. Is there a keyboard shortcut that does this?
Thanks in advance.
CTRL+; then SPACE then CTRL+SHIFT+;
Portland, Oregon USA
wrote in message
Ctrl+ ; inserts current dat...Compare Now() to a European date
This is driving me nuts,
I have a list of certificates. In column B their expiry dates are
entered as Europeans, some at least, do. Like today would be 20080524.
I want these cells to change colour with conditional formating.
For instance becoming yellow when there is less than three months
between now and the expiry date, and then becoming red when there
is less than one month to expiry. Else they should remain without
I have read through a hundred posts dealing with similar needs and
seemingly fine replies, but I get nowhere with my particular sheet.
When I format my B cell as ...Formula for date field
1.I have simple dates in one column (say column A) .
2.In the next column(Column B) I would like the date five months after
Column A to be displayed.Eg if Column A has an entry of 9th June
2007,Column B should display 8th November,2007.
3.A simple formula does not do the job as this does not take into
account the different number of days in different months!
Your post is a bit ambiguous since you don't really say how the
different number of days in months should be handled.
In articl...Date Format turn to Year
I tried to convert the date to YEAR and then the year plus 25 Years
=Year(A1) I'm getting the result 1900 instead of 1965.
I tried to add 25 years later to 1990 from 1965.
Your help would be much apprecated.
What's in A1?
Are you sure it's a real date?
> I tried to convert the date to YEAR and then the year plus 25 Years
> =Year(A1) I'm getting the result 1900 instead of 1965.
> I tried to add 25 years later to 1990 from 1965.
> Your ...using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering
if we can take it 1 step further?
For Activity Dates prior to 2/1/2007 they are using a normal reporting year
and the formulas below take care of Activity dates >2/1/2007?
So for example prior to 2/1/2007
1/1/2006 would have a B_Qtr of 2006-1
1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q")
2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate]))
3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q")
Than...Today's date on an Active X Calendar
Could anyone tell me how to set the properties so that the ActiveX calendar I
have in the database, displays the current date when the program is openend.
I thought this would have been easy, but obviously not!
Thanks for any help.
> Could anyone tell me how to set the properties so that the ActiveX calendar I
> have in the database, displays the current date when the program is openend.
> I thought this would have been easy, but obviously not!
> Thanks for any help.
Jame...Compare 2 different workbooks with the result in a 3rd
I have two workbooks (2005 Sales, 2004 Sales), which track daily results in
half hour intervals. I want to be able to show the increase in 2005 in a 3rd
workbooks. The first two workbooks are identically formatted. How can I do
this? Many thanks to all in the forum who have helped in the past.
If the data is in exactly the same position in the two worksheets you could
copy/paste one's data to a new worksheet and then copy the second's, doing
an Edit, Paste Special, Subtract on top of the first's data. This is
admittedly crude but it is easy to do.
&q...How do i sort rows randomly?
I want to choose 50 random rows from 10,000 lines of data and paste it into a
new sheet. The only way I know is to use a random number generator to
randomly select the records and then copy/paste the data out out, row by row,
fifty times, which is time-consuming. Is there a way to randomize my entire
data table by row so that I can take the first fifty rows all at once and
know that they've been randomly selected? Thanks. Jeremy
> I want to choose 50 random rows from 10,000 lines of data and paste it into a
> new sheet. The only way I know is to use a random numb...Getting right date value
I setup my DTPicker control to be used only
as a date control, yet I'm noticing that sometimes
it will give back a date AND a time all in the
same "value" variable.
Since it appears that a variable of type "Date"
can give back both a date and time, how can I
eliminate the time half of a date value???
I might not be able to exactly control the DTPicker
control to give me JUST a date, so I'm just curious
what to do if it gives me back both a date & time.
Try this :
Dim x as date
x = cdate(clng(DTPicker1.value))
&qu...Rounding actual results
I've got a grid that says:
Maths: 5 25
English: 7 25
And so on.
I'm wanting to work out how much time is devoted to learning in the whole
week. What I've got so far is:
Total learning time: =A1+A2...
all totals in
My current answer for hours is 18.
I'm stuck with what to do with the minutes column. so far, I've got an
...I need a formula to sum column b if column a is between two dates
I have an excel spreadsheet with employees time off. I need a formula that
will add column b if column a is betwee two dates. For example: if column a
is between 9/22/04 and 9/21/05 then add column b. I have tried all different
formluas but can't get this to work.
...Show a blank result in a cell when there is no value in the "Lookup" cell
I apologize if this question has been asked, but I have been unable to find
an answer searching the topics.
I'm using the following formula in cell C3:
When I type in an employee ID in A3, his/her last name shows in C3.
However, when there is no value in A3, C3 shows error "#N/A".
Is there a way to show a blank cell in C3 until a value is entered into A3?
Thanks in advance!
On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote:
> I'm using the following formula in cell C3:
> =INDEX(LastNam...Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and
formatting for new Word documents for a night school course I'm
enrolled in. Among other requirements for all papers is to put the
date the paper was created at the top.
I'd furthermore like it so that if I need to reopen the document after
creating it to say print another copy, the date written at the top
will not change. In other words, when I create a new document using
the template the current date is put near the top, but when I
subsequently open the file for editing or reprinting it does NOT
automat...Propose a meeting with multiple dates in Outlook
How do you propose a meeting with multiple dates?
> How do you propose a meeting with multiple dates?
"S" <S@discussions.microsoft.com> wrote in message
> How do you propose a meeting with multiple dates?
See this: http://www.slipstick.com/calendar/pickmeeting.htm
Brian Tillman [MVP-Outlook]
On 3/11/2010 7:16 AM, S wrote:
> How do you propose a meeting with multiple dates?
A third-party solution called Tungle works nicely for this.
ht...Null date parameter
How can I pass a null value to a date parameter in a Sub?
> How can I pass a null value to a date parameter in a Sub?
you have to pass it as Varian as normal data types cannot be Null:
Public Sub yourSub(ADateNullable As Variant)
On Local Error GoTo LocalError
Dim DateValue As Date
If IsNull(ADateNullable) Then
DateValue = CDate(ADateNullable)
If Err.Number = 13 Then
' Type mismatch.
--> stefan <--
...Serial Date in Multiple Worksheet
Hi, I need to create a workbook with the same Multiple Worksheet (TAB) form
on it. This form will be used for each day of the month. The problem is I
want to be able to enter the date on the first TAB and has the date replica
to each worksheet add 1 day to each sheet.
The first sheet - Monday, December 26, 2005; the second - Tuesday, December
27, 2005; the third - Wednesday, December 28, 2005; and so on.
Thanks in advanced.
Try running the sub below (Sub is by Dave Peterson, and was plucked from a
post by Gord Dibben in .newusers)
Here's how to set it up ..
In a new b...Date & Time
I have a form called frmDetails and I have set a text box (txtDate)
the form with the control source of =Format(Now(),"dddd"", ""mmm d
yyyy"", ""hh:nn:ss ampm") but the problem is I want this Time & Date
to be recorded in the table (called tblDetails) as it needs to be
for referancing when entries were entered into the database.
Can anyone tell me how to do this.
Control source should be the table column.
Make the default value your Now() etc.
In Excel 2002 is there a way of sorting sheets other than by dragging?
See www.cpearson.com/excel/sortws.htm for sample VBA code to sort
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"rifleman" <firstname.lastname@example.org> wrote in message
> In Excel 2002 is there a way of sorting sheets other than by dragging?
"rifleman" <email@example.com> wrote in mess...Transaction dates a day early
Using M06 with direct connect & bill pay to my bank, the downloaded
transaction dates entered are a day early in Money. If I view my account
through my bank's web interface, the date would show as I'd expect (e.g.
11/21) for a given transaction. But when viewing my account in M06 (again
directly connected/downloaded from bank), that same transaction has a date
of 11/20. All transactions from this bank seem to "post" a day early in
I do let Money change the transaction date from what I entered to what the
bank says (that's the way I want it). I a...input date
is this possible: i will type 011005 and then excel will automatically
format it as 01/10/05 and will be treated as date? thanks.
Only with VBA code or a function in another cell, there is no way with the
user interface of doing this automatically
Microsoft MVP - Excel
"rufino palacol jr" <firstname.lastname@example.org> wrote in message
> hi all,
> is this possible: i will type 011005 and then excel will automatically
> fo...Date Elimination
I have a worksheet with mainly dates in column A in the format of '25
Aug 2008'. Is it possible with a macro or similar to delete lines
beyond a certain date (2 years hence)? Basically, I'm not interested in
data more than 2 years old.
This would eliminate a lot of data and make for a more viewable worksheet.
Do Until ActiveCell.Value =3D ""
dt =3D Date - 730
If ActiveCell.Value <=3D dt Then
End ...sort special text/numbers in format with many dots
I need your help with sorting in Excel!
I have mani Text fields with numbers into it.
And it should sorted like this
How can I sort this like numbers? My problem is, that not all Numbers have
the same format as x.x.x.x! And I can't change this Text-Fields to Numbers,
because 10.6.1 looks the like 37052 :-(
With your data in column A, insert a blank column at B.
In B1 enter