Date compatibility error
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
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 ...Dynamic Ranges
I need to develop a function which uses a range. The range is dynamic based
upon a Lease Term.
Example: I have a 5 year lease - The range will encompase 5 cells (A1:E1).
I will always know the starting Cell.
Any help would be much appreciated.
Not sure i understand the question - what do you want to know?
do you want to know how to develop a function that uses a range - if so, any
do you want to know how to create a dynamic range - if so, check out dynamic
ranges at www.contextures.com/tiptech.html
"ACase" <A...Problem copying named range
I have a named range on a Lookup sheet which I am trying to copy to a
destination on the main sheet (Mobiles).
The range named PlugIn occupied V4:Z4 on the Lookup sheet and has a border
round the entire area (none internal). This is a region title and only has
text in V4.
Using the following bits of code to copy the range to V6 on the Mobiles
sheet, I end up with the text in V6 as expected, but the border only
surrounds V6, not V6:Z6.
Set PlugIn = .Range("PlugIn")
PlugIn.Copy (Worksheets("Mobiles").Ran...inserting columsns variable range
i have a spreadsheet that is variable in range (number of columns can vary).
i would like to create a macro that for each column in the worksheet, two
new columns would be inserted to the left.
i thought this would work, but i am running into problems...
LastColumn = Cells.Find(What:="*", After:=[A1], _
LC = LastColumn
For i = 2 To LC
If Cells(5, i + 1) <> "" Then
...changing date format
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'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.
then in the 'type' box, enter
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.
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?
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...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".
* 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.
...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.
"TONY" <email@example.com> wrote in message
> 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?
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
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
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.
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
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,
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
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
Any Idea what happened to it?
Sorry I found it.
> 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
>I had a question working about date and time it had 11 or 12 posts and
> 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
It's a Windows setting. Change it in the control panel.
HTH. Best wishes Harald
"Will Fleenor" <firstname.lastname@example.org> skrev i melding
> 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
> Will Fleenor
...Locating variable range to copy
I am an ordinary accountant but not familiar with macro & VBA. I have a
file, I need to copy a source formula to a certain range of which
starting position has fixed already and ending position would be
altered regarding to condition changed.
For example, say
source formula : locating at cell G11
conditions : range of row containing data in column F starting
Says now, there are 10 data starting from F11 to F20, therefore I need
to copy the source formula from G11 to G20.
Same as, when there are 100 data starting from F11 to F110, I need to
copy the source formula from G11...How do I copy formula but only increment certain ranges?
I want to copy a VLOOKUP formula down down the worksheet. When I use the drag
handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one
of the values but i want the other to stay the same.
How do I do it?? Please help
A preceding $ indicates a part of the address to hold constant, as in
$A1, A$1, or $A$1 to hold the column, row, or both constant.
> I want to copy a VLOOKUP formula down down the worksheet. When I use the drag
> handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one
> of the values but i want the ...Return column number of matching cell in external range
Cell A1 in open_workbook_1 contains a text string.
The same text string will be found somewhere in closed_workbook_1
within the cell range A1:Z100
How can I return to cell A2 in open_workbook_1, the column number of
the cell in closed_workbook_2 which contains the text string?
> Cell A1 in open_workbook_1 contains a text string.
> The same text string will be found somewhere in closed_workbook_1
> within the cell range A1:Z100
> How can I return to cell A2 in open_workbook_1, the column number of
> the cell in closed_workbook_2 which contain...formatting a date in a text field
I need to format a date that is in a text field - ex. 20081108 needs to be
11/08/2008 - how do I do this?
p.s. I cannot change the field from a text to a date - it deletes all the info
First, to change the text field to a date format:
It would probably be best to change the field to date type for programming
and reporting down the line. To do this (first backup your data) then add a
new field to your table (with a type of date). Do an update query to
populate the new field with an ...How do I display the most recent of three dates in an Access quer.
How do I display the most recent (or latter) of three dates in an Access query?
I'm assuming that the date field is truely a date/time datatype and not a
text field with something that looks like a date.
1. Create a query and sort on that field in descending order. Run it to make
sure it looks correct.
2. Open the query in SQL view. Change where it says SELECT to SELECT TOP 3
One caution: If there is a tie for 3rd place, Access will show all the tied
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
...Why does this not work (selecting a date) ?
SELECT CDate((Month(RecordDate) & '/' & Day(RecordDate) & '/' &
Year(RecordDate)) AS RecDate
This converts 8/31/2007 to 8/1/2007
Works fine for me with 2003 SP2.
You omitted a closing parenthesis.
Build a little - Test a little
> SELECT CDate((Month(RecordDate) & '/' & Day(RecordDate) & '/' &
> Year(RecordDate)) AS RecDate
> This converts 8/31/2007 to 8/1/2007
Where is the missing parentheses? I count 4 opening and 4 closing???
"KARL DEWEY" wrote: