When I enter the date format of choice 051607 and hit the return tab I get
this format 17-Apr-45
How could I fix this format, I have over 300+ days to correct
Please advise ---- Thanks for your help.
Are sure you don't get 16-Apr-41?
The reason this is happening is that dates are really just numbers formatted
to look like dates. The dates are measured in increments of 1 since a base
date. The default base date is Jan 1 1900 so its value is 1. Jan 2 1900 has
a value of 2. Jan 1 2007 has a value of 39083. It's the 39083rd day since
the base date.
So, when y...Previous Date
My data looks like this:
RunDate Customer SoldDate
03/26/2007 ABC 02/26/2007
03/26/2007 ABC 03/15/2007
04/09/2007 ABC 03/31/2007
04/09/2007 ABC 03/28/2007
04/09/2007 ABC 04/05/2007
Obviously I can retrieve records associated with a RunDate of 4/9/2007 by
selecting the max date, however I want to select the records associated with
the RunDate just prior to the max date, in this case 3/26/2007. Any way to
do this without actually creating a form to let the user select the date?
I'd...Excel date problems
Our company uses a report writer/formatter that creates Excel
spreadsheets and then sends them as email attachments to specified
users. The data being formatted is coming from a file shipped to the
report software from an I5 environment.
The data coming in is in the format mmddyyyy but some of the cells in
the SS are being converted to ddmmyyyy.
When this problem occurs it only happens for months 10, 11, and 12.
These dates are being used in calculations so this is a big problem.
We are using Excel 2003.
...Limiting input by date
Is there a way to stop a worksheet from accepting data after a certain date?
I have a workbook with 26 worksheets, 1 for each pay period. I would like to
add some kind of date limit so that the users of the workbook must go to the
next pay period if it is after the date that "sheet" closed.
You can use
Data | Validation
Just set your end date to be equal to some cell on that sheet where you
input the close date.
> Is there a way to stop a worksheet from accepting data after a certain date?
> I...Sorting and Date Format Help
I hope this makes sense, but without going into a lengthy detail of what I
need to have work, is there a way to take a date and either use said date, or
add either 30 or 90 days to it and format it as mmm-yy and still be able to
sort and group it without the interference of the day portion of the date? I
have the formula for the calculated date working as needed. Now (I think) it
is a formatting problem with the date.
I need to sort a second field and the day portion is causing it to not sort
Class CalculatedDate Shown in Formula Bar
AKU ...Money 2004 Small Business: Invoice Date vs. Due Date / recurring invoice
I'm trying to set up a recurring invoice to be sent out on a monthly basis.
The only way I figure to do this is by entering it into bills and deposits
but that wont create an invoice for me automatically. Is there a way?
Also, when I create a new invoice, it asks for the date that is pre-filled
with todays date. That would be fine if that were the Invoice Date but when
viewing the invoice preview, the Due Date is taken from that field also.
Therefore, Invoice Date is the same as Due Date. That ain't gonna cut it.
I need to bill 2 weeks in advance. Any thoughts?
...Add date/time to notes
Does anyone know how to add the Created Date to the notes form? I'd
like the created date to be visible when I view the notes on an
Account, Contact, etc. I can not even find where to edit this form.
http://<servername>/Tools/ViewEditor/viewManager.aspx?id=<GUID from Notes
Associated Vewi> in SavedQueryBase
"T" <email@example.com> wrote in message
> Does anyone know how to add the Created Date to the notes form? I'd
> like the created date to be visible when I view the notes o...Calculating Dates
Ok, If I want to calculate the difference of a date with the following date
in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value
in B1 of 35. How do I do this? Thanks!
In Excell the formula looks like this:
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
> Ok, If I want to calculate the difference of a date with the following date
> in Access: so...Formatting negative numbers #2
I have a text file that gets imported into Excel on a
regular basis. The negative numbers come into the
spreadsheet in this format: 4000.00- and I want to know
if I can write a macro to change all those instances
to: -4000.00, moving the negative sign to the proper
place. Anyone know if that can be done. In the past we
used an old dos application to do that prior to importing
it into Excel, but I want to get away from that if we
could and just let Excel do it. There are other places
in the file that contain text with dashes in it, so I
want it only to find the numbers with neg...Cell Format changes when data is cut from cells
I have a formated worksheet that I use to print data to pre-printed
forms. Each column of the worksheet is formated for Font, Alignment etc.
When I cut information from one area of the worksheet to another, the
formatting in the cells that I have "cut from" seems to revert back to
default values. Is there any way to prevent the formatting from going
back to defaults?
The simplest answer is you'll have to add an additional step.
Instead of "Cut", use "Copy", then go back and use "ClearContents" on the
I have a customer who runs the standard intrastat reports. A problem has
arisen in that some invoices are showing on the report and some are not. The
invoices not showing have a tax date outwith that month but were posted in
the month so that may be why they are not showing on the report.
Surely the report should show all invoices posted in that month that are EU
transactions. If it only shows invoices that have a tax date in that month
then what happens when you get a late purchase invoice with a tax date for
last month but posted in the current month, this will not be shown on th...Date Format Problem #3
I've imported a set of data from an external source into
Excel. One of the fields is the date and it is displayed
in Excel as:- 2003-03-25 11:34:04.
When I try to change this however to 'dd-mmm-yy' format
there is no change. It also means that calculations based
on this date can't be made.
If I double click the cell with the '2003-03-25 11:34:04'
date (as if I was to edit the contents) and immediately
hit enter (thus not making any changes), then the format
does change to the 'dd-mmm-yy' format and calculations can
Is there any way ...Date display on chart
I am using Excel 2007, Windows XP Sp2 and I am a newbie to Excel.
I have a diabetic program so when my readings are entered into a Palm T3
pda, after syncing, the program uses Excel 2007 to chart the results.
Looking at the data cells, the date is formated as dd.mm.yy.
Looking at the bottom of the Excel chart, it only shows dd.mm and no year.
I would like the chart to show mm.dd.yy when Excel graphs my diabetic
When opening the data file, a pop up says it is a "read-only" file.
Am I able to change the bottom of the chart to show the dates as mm.dd.yy?
If you can, then ho...Help! Wherecondition date nightmare
ok im starting to get the hang of passing queries through the wherecondition
instead of queries but this one has me stumped
Dim stDocName, strdate As String
strdate = "[Date to Arrive] =" & Me![Text28]
stDocName = "Time Details1"
DoCmd.OpenReport stDocName, acPreview, , strdate
i added a watch to strdate which comes back
"[Date to Arrive] = 19/10/2007"
so i no that its picking up the correct date however the report returns
i know the records are there for this date so i checked if the date
formating was the same this ch...Duplicate Item Lookup Code
1 of my stores in a HQ setup happens to have different items with the same
itemlookupcode. However, when browsing the store db, the HQID and ID fields
in the item table are different.
What is the proper way of fixing the duplicate itemlookupcode.
...Applying days of the week to dates
Does anyone know how to make Excel show the day of the week (ie Monday,
Tuesday etc.) given a specific date?
Just format the cell Custom, "dddd"
"will" <firstname.lastname@example.org> wrote in message
> Does anyone know how to make Excel show the day of the week (ie Monday,
> Tuesday etc.) given a specific date?
...Consecutive dates using two cells at a time.
I want cells a1 and a2 to have a date of Jan. 1/09 then have cells a3 and a4
to be Jan.2/09 etc.. in this consecutive pattern for the entire year. Is
there any fast way to accomplish this other than manualy entering the date
two cells at a time? Any help much appreciated!
On Wed, 14 Jan 2009 10:52:05 -0800, B.G. - Canada <B.G. -
>I want cells a1 and a2 to have a date of Jan. 1/09 then have cells a3 and a4
>to be Jan.2/09 etc.. in this consecutive pattern for the entire year. Is
>there any fast way to accomplish this other than manu...date string as sheetname
I have a macro that copies the last sheet to a new sheet at the end. I
would like to note the date that is in A2 prior to the copy, and add
one month to it.
This result will then become the name of the new sheet in the form
1-Oct-05 or something equivalent (ie: 1 Oct 05, 1Oct05,...)
How can I get the date from A2, and how can I modify it to the correct
Dim WB As Workbook
Dim SH As Worksheet
Dim sStr As String
Dim i As Long
Set WB = ActiveWorkbook '<<======== CHANGE
i = WB.S...Format million
Does anybody know how to get Excel to format in the
millions. So that 4180000 = 4.18m.
Format Cell/Number/Custom - copy/paste this :-
Message posted from http://www.ExcelForum.com
try the custom format
>Does anybody know how to get Excel to format in the
>millions. So that 4180000 = 4.18m.
...Formula or Format Error??
Can anyone tell me please why this formula is returning a 4 figure % in my
destination cell Q13 even though I have cleared the cell to "No Decimal
If the result of this:
Is an integer and the cell is formatted as PERCENTAGE then that will happen.
...Conditional Formatting #4
How do I set a conditional formatting?
Choose Format/Conditional Formatting... Enter your condition using the
condition 1 dropdown(s)/textbox(es), click Format, and choose your
format option from the Font/Borders/Patterns tabs.
For more help, see "Conditional Formatting" in XL Help.
In article <email@example.com>,
"GIO" <firstname.lastname@example.org> wrote:
> How do I set a conditional formatting?
keep in mind that there is a limit to the number of conditions you ca...Cannot format cells
I have a spreadsheet that will not allow me to format
most of the cells. If I right click on any cell or range
of cells and pick Format Cells nothing happens. The
dialog box does not open.
I can format some of the cells on the sheet but there is
no rhyme or reason to it.
There are some borders of differing types and this seems
to be the problem as I can copy the spreadsheet to a new
page, delete all the borders and then re-do them
identically to the original and then the Format Cells
dialog box works.
I can copy a format from one cell to another and that
There are no m...Access 2003 Code Commenter Add-in
I've just upgraded from the XP version to Access 2003. Trying to add
standard error-handler and comments to my code, I realized that Code
Commenter and Error-handler add-in is no longer showing up under Add-In menu.
Is there anything special I need to do to continue using this add-in?
My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.
In the VBA Editor window (alt+F11), you can use the
Comment Block and Uncomment Block commands on the toolbar. These buttons
allow you to Add and remove the comment character, an apostrophe, for each
line of ...Setting dates that change with main Project date.
I have a task in MS Project that needs to be set 4 days before the
finish and should automatically change when the project finish date in
Project Information gets changed.
This project is a party event that has been set to a finish date (the
date of the party). If I set a constraint on my specific task item that
needs to be done 4 days prior to the finish date, and I change my
Project Information Finish Date (since I am basing my project on a
finish date) to a week or a month later, my task item with the
constraint remains constrained to that date and does not change to
reflect i...caculate the total for specific date
I would like to caculate the total in column A for those date more than 21
days until today in Column B, can anyone help?
thanks a lot!
=SUMIF($B$1:$B$10,"<=" & TODAY()-21,$A$1:$A$10)
"Lowan Chan" <LowanChan@discussions.microsoft.com> wrote in message
>I would like to caculate the total in column A for those date more t...