Coping with missing year value in date data type field (Rephrased/Repost)
Suppose I am collecting Date of Birth data for a contacts application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to deal with
this? I can think of two, but...
1. Use a "dummy" value for an unknown year, (e.g. 1900). This would allow
the use of a date/time data type and be fairly easy to implement, but it
sure feels like I would be breaking some kind of relational rule to enter
2. Create three text fields (txtDay, txtMonth, txtYear). This would allow
the use of...Date Calculation
I have two fields in a query: "Date Completed" (mm/dd/yyyy) and "Time Used
(hours)" (nn.n). I am trying to calculate a third "Date Started" that I want
in the format mm/dd/yyyy. Assuming an eight-hour day, I am trying to use the
expression [Date Completed] - [Time Used (hours)]/8, but I can't figure out
which Date/Time functions to use to convert my dates and hours to the same
units to do the calculation and then convert back to a date again.
I am using Access 2007 and am not very competent with it. Thank you in
advance for your help.
One ...Formula for a Check Register function #2
Your right, sory about that.
Here is the basic layout I am going for
_Withdrawal______ | _Deposit_ _____| _Balance_ (total)
Basically just like a check book register. I need the Balance column t
display the total of the Withdrawal(negative) and the Deposit (positive
added to whatever the balance was in the previous row. Also, isnt ther
a better way than to manually alter the formula in the Balance colum
for each new row? Again thanks for ...Highlighting specific dates in a graph
I have a 2d line graph with 2 series of data graphed against a time
axis. I need to highlight points on both series at specific dates with
the date data from another sheet. Excel 2000.
Message posted from http://www.ExcelForum.com/
See my response in the charting group.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
pce500 < wrote:
> I have a 2d line graph with 2 series of data graphed against a time
> axis. I need to highlight points on both series at specific dates with
> the date data from...How do I get a number count of dates that fall between specific dates
I need to get a count of dates that fall in between for example A2 an
If A2:A12>38133 and a2:12<38138
Thanks for the hel
Message posted from http://www.ExcelForum.com
For everyone's benefit keep the discussion in the newsgroup.
"JohnB >" <<JohnB.firstname.lastname@example.org> wrote in message
> I need to get a count of dates that fall in between for example A2 and...Date issue
I have a sql database that access is using to do some queries on, in the
queries there is a date/time field. I've used the datevalue() function to
remove the time values. In a different query the user will enter a begin
date and an end date when I run the query I get dates that do not belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008. Also the sql
database list the date/time field as 01/01/2008 access list it as 1/1/2008.
Thanks in advance
From your description, it almost sounds like the so-called "date" is
actually just a text value. Do you...GMT date & time
Can someone tell me how to save the current GMT date & time into a cell?
The reason I use GMT is we have daylight saving here and I would like the
time to be independent of daylight saving.
> Can someone tell me how to save the current GMT date & time into a
> cell? The reason I use GMT is we have daylight saving here and I
> would like the time to be independent of daylight saving.
See http://www.cpearson.com/excel/timezone.htm for details of how to get UTC
First you need to know your timezone and variation from GMT, a google
search...How to format a date to a different format
I need help in converting a date "20050205" where the first 4 numbers are the
year, the 2nd two numbers are the month, and the last two would represent the
day of the month. I have tried several things to format these and looked in
Help but I am not asking the right question. I want to be able to have the
data read 02/05/2005 or something close such as 2/5/05. The date format it
is currently would be fine if that was the only report, but I have several
other downloads that I work with and those dates are in the format I am
requesting. Also the original format is hard to re...contracts with no end date
We have hosting contracts with our clients that are on a month-to-month
basis until the client cancels. Since a contract requires an end date, I am
wondering how other people are dealing with this. Do you simply set the
contract to expire way in the future? I was thinking that I would simply
change the requirement on the expireson attribute, but Microsoft marked it
read-only and it is a system required field.
...Number formats default to dates
I have a user that every time he opens a workbook, the columns that were
numbers default to dates, he changes the format back to number & saves. Then
when he opens it again its back to dates - where is the setting that is
defaulting the cell format to dates?
> I have a user that every time he opens a workbook, the columns that were
> numbers default to dates, he changes the format back to number & saves. Then
> when he opens it again its back to dates - where is the setting that is
> defaulting the cell...Install not properly registered?
Clicking the Publisher shortcut starts the msi-window asking for de Office
2000 cd 2. Only after that the prog will open. The other Office applications
are opening just fine. All the updates are installed, OS is Windows XP
Professional SP2, also all updates included. I have done two un- and
reinstalls of Office 2000, I have also done two System Restores. I have
searched MS Knowledge Base, I have visited a couple of MVP-sites, I have
googled but didn't find a solution. What to do now?
Have you only reinstalled Publisher which is on CD 2? Have you tried a detect and
repair in ...How to update the Year in a date field?
----------------------------SQL SERVER 2008 Database-----
The AP clerks forgot to change the year from 2009 to 2010, so they did
lots of data entry with invoice dates instead of 2010, they added 2009
and the date field looks like this "2009-01-16 00:00:00.000", is there
a way to replace 2009 with 2010.
Please help me and thanks in advance,
On Mar 9, 4:19=A0pm, DVR <dvramesh...@gmail.com> wrote:
> Hi All,
> ----------------------------SQL SERVER 2008 Database-----
> The AP clerks forgot to change the year from 2009 to 2010, so they d...auto fill last used date
I have a form that requires entering the same date multiple times (not
necessarily todays date) how can I have it autofill the date that I enter the
Here is a link that explains it. I would put the code in the After Update
event of the control for the date:
Dave Hargis, Microsoft Access MVP
> I have a form that requires entering the same date multiple times (not
> necessarily todays date) how can I have it autofill the date that I enter the
> first time?
...GP Date Error
Our Dynamics GP90 users ARE GETTING THE FOLLOWING ERROR AT THE TIME OF
LAUNCHING GP,the following is the error
Unhandled object exception:
Type mismatch or the value "31-03-2007 12:00 am" in the condition is not
Kindly assist me in resolving this issue.
Can you Verify your Regional Setting under control panel and set it as per
the location taken in case of Installation of Dynamics GP.
Ex: if installtion is Middle East i will set my control panel --> Regional
Settings to dd/mm/yyyy and date to hh:mm:ss etc.
...Help ASAP. Changing number string into date?
I pull reports from an internet site into excel for state reporting purposes
and need to calculate the duration from one date to another. The problem is
when I pull the table in it chages the dates to a string (i.e. 06/02/2001
looks like 6022001, and 12/02/2001 looks like 12022001). Is there a formula
that will take the 6022001 and 12022001 and chane them into date format? I
tried the format tab and it wouldn't work.
You need to include an example date that shows us where the day/month
I believe all US dates are m/d/y, but UK, Australian, etc. dates are d/m/y.
Use a...calculating weekday date
I need to calculate a date 10 days from a start date that, if the final date
is a Sat, the result defaults to the previous Fri, or, if the final date is a
Sun, the result defaults to a Mon. Is this possible? I use 2003. Thanks for
your help. In your response, remember I am a novice and need a little extra
On Tue, 10 Jun 2008 19:23:01 -0700, Khardy3352
>I need to calculate a date 10 days from a start date that, if the final date
>is a Sat, the result defaults to the previous Fri, or, if the final date is a
>Sun...Date Format 12-11-09
I have been using the following macro to change "20100205" into a date format.
Now I am also needing to change "10" to a date format so that I can have it
show "Oct" in a given field. How can I do this?
Selection.TextToColumns Destination:=Range("z2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5), TrailingMinusNumbers:=True
End Su...import/download date not changing-no support for trial ver?
M05 Deluxe trial, upgraded from M02.
I posted this question on 10/10 and recieved a suggestion that doesn't apply
to me. I don't use any of the
online banking features offered by my Credit Union. I only export files and
import them into Money. The suggestion
didn't work because M05 (correctly) does not show any banks set up for
I really don't want to purchase the software unless this is corrected. But
I'm thinking I don't have any online
support offerings, other than this newsgroup, because it's a trial.
What I'd really like to do is send my...Date & Time Stamp does not update
I have created a form in a Public Journal Folder. When I
add a new Journal Entry the date and time is the same as
the date and time that I created the form. I do I get the
form to update?
...Dates in Excel
My accounts software, Quickbooks, exports data to Excel but it really
cocks up on dates. In Ireland, I enter dates as dd/mm/yyyy. In the
exported data the actual digits stay in the same order but are taken
to be USA style mm/dd/yyyy.
So 01/01/2004 is 1st Jan 2004 in Quickbooks, but exported as and
understood by Excel as Jan 1st 2004.
02/01/2004 is 2nd Jan 2004 in Quickbooks, but exported as and
understood by Excel as Feb 1st 2004.
13/01/2004 is 13th Jan 2004 in Quickbooks, but just a text string in
Excel because no 13th month exists.
At present I save the exported file in Excel as a .TXT (Ta...how to update the top folder Date Modified
How can I look at the top level folder date modified, I can tell any item
inside all the sub-folder was updated or created? So the top level folder
'date modified' is the latest date.
Current, in Windows, even the inside sub-folder is updated, the top level
folder date is not changed.
How do I make the top folder's Date Modified reflect the latest modified
dates of any items inside the sub-folder?
That is if any inside item inside multiple levels of sub-folder is modified
and updated, how can I make the Date Modified updated for all the upper
levels folders, or j...Dates from Excel not Importing Correctly into Project
Sometimes dates are not imported into Project as they are entered in Excel.
For example 01 December 2007 was imported as 30 November 2007 when imported
into Project. other dates can be 2 to three days different than what's in
Excel. Any ideas?
Sorry, but this newsgroup is for questions about Access, the database
product that's part of Office Professional.
I'd suggest reposting your question to a more appropriate newsgroup: one
related to Project or Excel.
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"Bendinblues" <...2003 insisting on using US Date Format
There I was, plodding along, setting up a flowchart. I was just about to hit
print, when I noticed that the dates were set to US format (which could have
caused real confusion, had it been yesterday or earlier!) My entire system
is set to UK - including the Office Global Language settings.
Is this a known issue with Visio - I seem to recall something similar with
FrontPage a couple of versions ago where it would only spell check in
American English (irrespective of what flavour of English the system was
This doesn't reflect well on me when I'm trying to put together a
profess...Help with Dates on Y Axis of a chart
How do I get my chart to display the dates that I have in my Excel Sheet?
For example I have a line chart with a set of 7 dates: 01/13/03, 09/12/03,
11/06/03, 05/11/04, 06/03/04, 07/08/04, and 07/06/05. My dates incriment
every two months, which I do not want. I would like to have the dates above.
What do I need to do???
Right-click around the chart and select Chart Options. On the Axes tab,
select Category for the primary axis.
> Hi Guys,
> How do I get my chart to display the dates that I have in my Excel Sheet?
> For example I ...remove dates from chart
I am new at creation of charts. I do not have the weekends in my data but
they keep showing up in my chart? Can they be removed?
In a line or column chart, the default axis type when Excel recognizes dates
in a time-scale axis. Change this to a category axis:
Chart menu > Chart Options > Axes tab, select Category under Primary
Category (X) Axis.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
"Rebecca" <Rebecca@discussions.microsoft.com> wrote in message