dates appear as error hashes
When I type dates in cells on an Excel spreadsheet they become a series of
hashes. But other dates of the same format in other workbooks are fine. Why
does this error keep on occuring. I think may be the cells are not in the
right format for me to add dates. Can i change this??
Try widening the columns. Does this resolve the problem?
> When I type dates in cells on an Excel spreadsheet they become a series of
> hashes. But other dates of the same format in other workbooks are fine. Why
> does this error keep on occuring. I think may be...Help with date ranges
I've got a sheet where I have some columns with dates in and I want a
column with the number of working days between them.
I've used the DAYS360 function, which gives a rough indication of the
number of days, but it's not very accurate. Can anyone give me an idea
of now to do it, a script, a plug in, or a different app I can download
to do it?
Help really appreciated.
Look at NETWORKDAYS and WORKDAY in help, they are part of office/excel but
need to be installed (Analysis ToolPak)
"MattG" ...how to get rid of default dates in Excel?
I want my dates to stay as I have typed them - not for them to be changed
for the Excel default system.
Then you must format the cells to your date entry style.
Either before or after.
BTW............Excel does not control default date format.
That is controlled by your Windows Regional and Language settings.
Gord Dibben MS Excel MVP
On Mon, 22 Mar 2010 07:51:01 -0700, Margaret L. B. <Margaret L.
>I want my dates to stay as I have typed them - not for them to be changed
>for the Excel default system.
2 optio...Compound condition with COUNTIF
I want to be able to count values between 2 dates in a table. The query will
look something like:
=COUNTIF('Roll Out Schedule
Can anyone tell me where I'm going wrong?
=COUNTIF('Roll Out Schedule 23-Nov-05'!$H$3:$H$953,">27/11/05-
COUNTIF('Roll Out Schedule 23-Nov-05'!$H$3:$H$953,">05/12/05")
(remove nothere from the email address if mailing direct)
"Mike McLellan" <MikeMcLellan@discussions.microsoft.com> wrote in message
In Money 2006,
In previous versions of Money you could set the check register to show
either the date an EPay was processed by the back or the date it was due at
the Vendor. In Money 2006 I can't seem to find that setting. Can anyone
direct me to the right place? Thanks.
...Using Date as a Variable
I have cell contents and formulae in cells with fixed dates in them. I
need to create other versions of the workbook with different dates. So
I created a date variable. But when I run the procedure to replace the
fixed dates with another date, I get an error message:
Run-time error '1004':
Application-defined or object-defined error
Here is a simple example of what I have in the code.
Dim sCripDate As String
sCripDate =3D "2007,10,10"
.Range("D8").Value =3D "=3DDATE(sCripDate)"
I must be...How to Change Date Formats
I have a bunch of raw data that I am working with. One of the columns
displays dates and I am trying to strip out the year. If I try to use the
YEAR function I get strange answers back.
Cell reads: 1/1/2006
Answer using YEAR function: 06/1905
Answer that I want: 2006
My data comes from a CSV file, so I think maybe it is in a different date
format than excel uses as a standard. Any suggestions on how to reformat
this data or some other formula I can use to get the answer I am looking for?
If the cell containing the date is not recognized as a date format then you
might ...count records based on date range
I have a list of records about which I'm trying to summarize differen
information. One of the columns in the spreadsheet is a created date.
What I want to do is create three summary fields, one that shows a coun
of the records from today to six days ago, a second field that shows
count of all records from seven to 14 days ago and the last fiel
showing a count of all records over 15 days ago. This sheet will b
updated regularly, so I don't want to have specific dates in m
formulas. I used the COUNTIF function to do summarization for
different part of the spreadsheet, but I can'...dates and ages
I have a list of names and birthdates in the format:
I would like to add a third column that calculates the
ages of the persons listed based on todays date (in days).
Does anyone have any idea how to do this?
format as a number
Message posted from http://www.ExcelForum.com
Check out The DATEDIF Function.
Info at Chip Pearson's site.
Gord Dibben Excel MVP
On Tue, 22 Jun 2004 07:21:31 -0700, "mp" <firstname.lastname@example.org...Newbie here
I'm new to the group and do very little with excel. I'd like to do
the following just to to make my life easier at work. I know about
conditional formatting, but what I want to do is a bit too complex for
that, I think. Therefore I must need VBA but I've never used it
before. Any pointers please?
I am doing a sheet which will keep track of the training needs of a
group of people. Some training needs to be done once every 6 months,
some once a year and some just once every 2 years.
So I have names on the left in the rows and each column refers to an
item of training....Entering date in a cell so that it falls in specific way when sorted?
I have an entry that I know falls in February 2004 but that we don't
have an exact date for as that information has been lost (ahem, _not_
may I say by me [thank goodness!] <lol>). Anyway, all the other
entries for this date column are in the usu. m/d/yyyy format even
though they have a custom setting that has it display differently.
My problem is one I've run into many times before - how to have a,
say, 2004.02 entry (for Feb.2004) sort between the last January 2004
one and the first February 2004 one? Is it possible to enter the
Feb.2004 date in such a way that it will sort ...Excel 97/2000 - Autofilter
Im trying to find my perfect date using Excel (haha).
I have all my friends in a database, with Autofilter on.
Since I'm male, I first filter on the column sex=f, than second filter is on
the column married=n.
This works ok, all my non-married female friends are showing. Next I filter
on salary=top 3 and the result is empty!
I know that my non-married, female friends are not in the salary top-3 of my
database, but that is not what I'm looking for. Im looking for the top-3
salary unmarried girls!
What do I do wrong, this way I always stay single !
Ps. Yes I know, ...Date Calculations #3
Cell A1 contains accounting Period start Date say 1/2/04
A2 contains accounting period end date say 31/1/5.
A3 contains a different start date say 1/4/04 and cell a4 contains
different end date say 31/3/05.
I need a formula to tell me how many of the days in 1/4/04-31/3/05 fal
within the accounting period.
Thanks in advance for your hel
Message posted from http://www.ExcelForum.com
try using the datevalue(date) function, it returns a number value and i
the two are subtracted, it should work out to the number of days.
so if you take the time frame and find out how many days are in it...Cell colour according to date
Is there a formula that would change the cell colour if a date in a cell was
out of date afetr a set number of years.
Cell B9 contained date 140506
I then want the cell B9 to turn red when the date reaches 2 years later
Thanks in advance
Let's assume that your example date is 14 May 2006.
Click on B9 and:
Format > Conditional Formatting... > Formula Is > =TODAY()-B9>731
then pick the colour red.
Gary''s Student - gsnu200773
Use conditional formatting with a formula of
...Inventory Dates #2
If I am to add all the shipments for all different equipment types by
receipient and other criteria, eg
This formula is validating the receipient, the equipment type and even the
month it was shipped. Adding totals of all shipment by item description.
Our inventory starts on the 22nd of each month and ends on the 21st of the
Up to this point, I had to add a column to specify what month's inventory
the shipment must be applicable to, otherwise is considering the month to
start ...Date Ranges / Report / Displaying Data
I have a report named “rptLocal”. This report is made up of approx. 50
queries. How would I go about making it so that the report only displays data
ranging between two dates? I would like make it so that the user would enter
the date ranges into textboxes within a form “frmReport”, presses a command
button and poof the report.
Is this possible? And how?
Message posted via AccessMonster.com
50 queries in a report? Wow, that's a lot of different information. If you
want each query to be filtered by ...Help with Conditional formatting with Dates
I have a spreadsheet in which I have to monitor various dates which are in
different columns (i.e. (1) Date Tender Released, (2) Date Company Selected,
(3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...).
Would if be possible to Conditional Format and have diffirent ROWs
highlighted for each individual Contracts (I have 300 running contracts
running) every time a date is filled in these different columns:
Initial Step (1) = Yellow (Call for Tender released)
Step (2) = Blue (Company Selected)
Step (3) = Brown (Contract under Process)
Step (4) = Green (Contract Si...how to include real time & date on a worksheet
how to include real time & date on excel spread sheet
I was supplied with a small macro that would enter the current date/time in a
cell when invoked.
ActiveCell.Value = Format(Now(), "mm-dd-yyyy h:mm:ss AM/PM")
Is that what you want?
> how to include real time & date on excel spread sheet
...running total between two dates
I am trying to have my spreadsheet sum up purchases that fall between (month)
12th to (month) 12th, and when/if a new purchasing month starts, restart
summing the purchases. Column G are purchase amts. Column A are purchase
Originally, I just referenced month to month, with Column I formula:
=MONTH(A5) etc., and my running total forumla:
but because my purchasing dates are middle of the month, this is not working
I would like something I can fill down and not have to rewrite for each date.
I've looked through the...Turn off auto date recognition
Is there a way to keep Excel from turning text that might
represent a date into a date? For instance a user is
entering text such as june20, march08. If we format the
cells as text before entering it is fine until he tries to
use the Replace feature. During the Replace process Excel
overrides the text formatting. Is there anyway to turn
this off? We forced it with an apostrophe, but that seems
like a sloppy fix. Any suggustions would be appreciated.
...Excel ignores boot-time regional settings when interpreting a date
I have a third party DDE app that exports dates as strings, correctly
using the short date format in the regional and language settings,
which, in my case is MM/DD/YYYY (reset at every boot).
Unfortunately excel insists in interpreting that date as DD/MM/YYYY
[Application.International(xlDateOrder)=1, it should be 0],
disregarding my regional settings. The result is that all the dates are
wrong or #VALUES!
If this is not wierd enough, listen to this: it only happens after I
reboot (when the date format is reset to MM/DD/YYYY). If subsequently I
change the short date format in the regional se...Date some qualifies
Ok, I need help with a formula (if you have time). If I have someone’s hire
date and birth date, I need to calculate what date they will qualify for
retirement, either equals age 60 plus years of service = 75 or age 65
(whichever is earlier). Thanks!!
Excel 95 - Excel 2007
Northwest Excel Solutions
(Remove ^^ from email)
"sgwill" <email@example.com> wrote in message
> Ok, I need help with a formul...Calculated Field in Query
I have the calculated field below in a query. I was expecting to get the
results in the query (for this field) formatted as a date. But this is not
happening. Can anyone explain why, and suggest solutions?
Thanks in advance,
Relevant_Date: IIf([Payment Terms]![Counting
Terms]![Counting From]="Invoice",IIf(IsDate([Orders]![DC Received
Date]),CDate(DateAdd("d",[Payment Terms]![...Charting only the specified dates
I am using Excel 2002 SP-2 on Win2k Prof.
I have the following data for testing
And, when I simply chart it the chart shows missing dates on X-Axis, 25/9/04
and 26/9/04, with interpolated values each, according to either end of the
closest values (24/9/04 and 27/9/04). Why does this happen? How can I turn
In relation to Q1, I try to create PivotChart and this time need
interpolated values for dates not specified in PivotTable. How can I do this?
...Calculation of days between 2 dates
I have a problem: I want to calculate then amount of days between two given
A3: amount of days
How can i do that?
Thanks for any help!
=A2-A1, Format as General, not as Date, which Excel does automatically
"Jochen Munzinger" <firstname.lastname@example.org> wrote in message news:email@example.com...
| Hello everyone,
| I have a problem: I want to calculate then amount of days between two given
| A1: 01.02.2006
| A2: 01.03.2006