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...Using iterations to calculate circular references.
I am working with data that requires using circular references in some
calculations. I checked the "iterations" box to allow for these calculations
to work, and Ecxel seems to be calculating everything correctly. The problem
is that after I close the workbook, when I later reopen it to continue
working, all the cells that are a part of the circular reference display the
#VALUE! error. I can fix this by deleting one cell that is part of the loop
and then undoing that command, or by copying the formula from a cell,
deleting it, and pasting it back into the same cell. I have to d...varible calculation
I am trying to create a calculation for haulage/trucking charges
1-300 kilos £ 30
300-400 kilos £ 35
400-500 kilos £ 40
What formula could I use to accomplish this??
assuming that you mean:
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"kinsey" <firstname.lastname@example.org> wrote in message
&...calculating return in a range
I have a range of stock prices. with indication for buy or sell. and i
want to calculate the return of the range for example :
I want to calculate the return for the first buy signal. the range is
from the start to the first sell signal 537.54- 522.73
then the sell range untill the first buy signal 531.22- 537.54
and so on....
kman's Profile: http://www.e...SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade
each department would access a spreadsheet that would load data from a
common lookup spreadsheet
The loading process was performed as follows:
The lookup file and each department file would be opened to update the link
for that month and then saved and closed. When the department head would
open the department file, a question would appear asking if they wanted to
update the link, if they answered no the spreadsheet would open and display
the calculated data. If they answered yes, the file would o...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...How to display Total Item Quantity on receipt and Status bar
I would like to print a total Quantity of items purchased on receipt and the
I found a way of displaying them but the problem is that we use tag along
items for alcohol tax . This tax differs based on alcohol content and size,
example a 6 pk of beer has $0.20 tax plus sales tax, a 750 Ml a bottle of
wine has $0.116 tax .
If i ring up a 6pk or beer this would show up as 2 lines(line one name and
price for beer , line two alcohol tax tag along item) on POS screen and the
Total items and total QTY count shows as 2 when in reality only 1 item was
This gets even more conf...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...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.
...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...Show all months on report
I have a report that shows total shipments by month for specific items by
totaling in the Month Footer. Some items have no shipment on some months so
that month is not listed. Is there a way to have the report show all months
regardless if there were shipments that month or not?
Create a table name CountNumber with field CountNUM having integers from 0
(zero) through you maximum spread.
Build the query below.
SELECT DateAdd("m",[CountNUM],CVDate([Enter start date])) AS [My Dates]
WHERE (((DateAdd("m",[CountNUM],CVDate([Enter start date])))<=CVDa...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...how do I not show a record in a report when the total is zero?
I have a very simple DB where I track items. I have an ID, Description,
Intake #, Sold #, Remaining # (this is from subtracting the sold from the
intake), and a total inventory (this is calculated using Dsum on remaining
inventory based on description matches).
I have a report that I wish will show all records that have a remaining
balance, and just exclude the ones that have a zero balance.
Private Sub Report_Load()
If Remaining.Value <= 0 Then
ID.Visible = False
Description.Visible = False
Intake.Visible = False
Sold.Visible = False
Rema...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.
&g...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" <email@example.com> 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 ...Incrementing the date
I print out a register, 1 for each day of the month is there anyway of
incrementing the date for each one, so i don't have to change the date each
time i print it out.
Are you wanting the date you printed it?
> I print out a register, 1 for each day of the month is there anyway of
> incrementing the date for each one, so i don't have to change the date each
> time i print it out.
And do you put the date in a cell (what cell)?
Or do you put it in the header or footer?
I'm guessing that you want to preprint the forms for a month (like on the first