Validate the format of a number
I need to determine that an entered serial number is valid. It must check
that it is 11 characters and follows the format as follows:
a letter, followed by a number, followed by 2 letters, followed by 6
numbers, and ending with a letter.
For example, the user enters D7PM234567B and the cell next to it would
indicate 'valid' or something similar.
If 87PM2345674 was entered, it was indicate 'invalid' next to it or
something similar to alert the user it is not in the correct format.
Thank you very much in advance.
=AND(LEN(A1)=11,CODE(...Convert Date YYYYMMDD
I need to convert using a query the date format from MM/DD/YYYY to YYYYMMDD.
How do I do this?
mattc (at) saunatec [dot] com
Message posted via AccessMonster.com
Build a little - Test a little
"mattc66 via AccessMonster.com" wrote:
> Hi All,
> I need to convert using a query the date format from MM/DD/YYYY to YYYYMMDD.
> How do I do this?
>...View pointer in static function Help!!!
Help please , i need to implement a static function in CView derived class
that must return to CDoc derived class the active view pointer...how can i do?
i tried something similar
CSdicap10View* pView = GetActiveView();
it return error GetActiveView undeclared identifier
but it return static member functions don't have this pointer and also
CFrameWnd * pFrame = (CFrameWnd *)(AfxGetApp()->m_pMa...Date based coding
Need a little help. I have a form that the users use to enter in vacation
days onto a calendar. What I would like to do is allow them to make changes
to this calendar but prevent them from changing things after the fact.
Example if they were scheduled off on 11-16 they must change details for that
date on or prior to the 16th.
Is this possible?
to validate a record and prevent it from being saved, put
code in the form BeforeUpdate event
'----------------- make sure all required data is filled
'make sure SomeControlName is filled out
If IsNull...Want to format data
I have data in Excel that is in 2 columns as below
Coloum 1 Coloum2
I wanted to create my SQL script from these columns as below
Update apdoc set user1 = 'Coloum2 value' where refnbr = 'Colum1 value'
As I have thousands of lines I have tried concatenate etc but it does not
come out ok as the ' don't come out ok and then leading 000 disappear in the
I would really appreciate some help on this.
Is this an Excel question or an SQL question? Your post says you hav...Report using dates as a criteria
I have a register (list) that has headings such as name, start date, end
date, course duration in days (4)
Name Start Date End date Duration
Sid 25 Feb 2010 3 mar 2010 5
Sid 12 Mar 2010 16 mar 2010 3
As showed each name will have multipule lines as will attend many course.
What I need to do is consolidate the list to show how many days they
undertook training each month. The report has heading as below
Name Jan Feb Mar Apr etc
Sid 0 2 6
any and alll help gratef...XML formatting to HTML...beginner question
Hi, I apologize if I am posting this in the wrong forum. I am creating
a .net 2.0 website using vb.net. I have an XML file with the following
<lo number = '1'>
<Title>First Learning Objective. Click for more details</Title>
<details>These are the details for LO 1</details>
<lo number = '2'>
<Title>Second Learning Objective. Click for more details</Title>
<details>These are the details for LO 2</details>
<lo number = '3'>
<Title>Thir...Item Report by Date
looking for report that I can filter by date range that will show:
department, category, item lookup, description, quantity sold (even if 0),
quantity on hand, total sales amount $, profit margin %, profit margin $,
last sold date.
The movement report is close but I can only get it to show items that have
sold. I need to include all items even if they havn't sold. Any ideas?
...Date chart formula question?!?1
In a very old spread sheet i was using this formula
=IF(OR(AND($D7<G$6,$E7>F$6),AND(F$6>=$D7,F$6<=$E7)),".","") and it worked for
dates pre 2009. However, i now wish to use it for dates between 2010 - 2011.
Each column represent a week and the rows will indicate and project or task.
can anyone help?
There is nothing it that formula that will make it work for pre-2009 and not
for later years.
Perhaps you need to look at your data values?
If you have a problem, you could tell us what values are being fed into the
formula when it works...Formatting a range of cells doesn't work
I have a workbook exported by Access. Selecting a unique cell or a range of
cells the cell formatting doesn't work neither chosen from the menu nor
right-clicking on the cell (the formatting dialog-box doesn't come up at
all). Selecting an entire column or row cell formatting works normally.
What is the cause of this and how to fix the problem?
Not heard of this before, but if it's a single sheet you could Insert,
Worksheet, copy the old worksheet, select cell A1 on the new worksheet
and Paste Special, Values (assuming there was no intended formatting
I am using the Ccur function to convert a number to a
currency type. It works fine except in the cases where the
number is negative. Is there a way to get a negative
The case it fails on is this Ccur( 0 & [number] ). If the
field is blank...make it $0.00, else make it $number.
Fails when [number] is negative
>I am using the Ccur function to convert a number to a
>currency type. It works fine except in the cases where
>number is negative. Is there a way to get a negative
Thi...Issue with PROPER function
I have the following problem:
I am attempting to clean up some addresses using the PROPER function. This
works really well until I come to PO Box numbers.
My problem is that this makes PO Po.
I also have entries such as P O
Any suggestions on how I can do the following:
Make the rest of the address proper and not the PO part and also cater for
keeping P O as is.
Provide some Addresses as Sample.
> Hi there,
> I have the following problem:
...Displaying add-in function on ribbon bar in Excel 2007
I have a small Excel add-in that contains a library of standard functions
that I use on a regular basis. Is there an easy way for me to expose these
functions in some sort of list format on the new Fluent ribbon bar in Excel
The operant word here is "easy". I think someone is going to have to write
code for you to do this, I don't believe there is an easy way to do it, ie.
using one of the built-in features in Excel.
If this helps, please click the Yes button.
"Schizoid Man" wrote:
> ...COUNTIFS with a date for criteria...
I want to count all the entries in column N that occurred between a range of
dates specified in the other criteria. The dates are obviously in column B.
I use this currently-
However, I use this similar function in multiple cells, all counting
different attributes. Rather than going into each cell and changing the
search dates, I wanted a criteria to be a variable that can be entered into a
different set of cells... like this.
COUNTIFS(Data!N:N,"=Option",D...Problem with formula depending on date.
Soory, if this problem is already resolved.
I am trying to make a list of consumed goods (fuel, water, etc...) in
one month extracting data from DAILY report. It work, but only for one
(this very) day. When make report for another day, the OLD data on the
from cells on "monthly" list go to "zero value". Need to KEEP this
data, or, beter say, I dont need any formula in cells from yesterday,
enough is ONLY value. If I am not clear, please ask me to clarifie.
Attachment filename: formula.jpg
Download attachment: http://www.excelforu...Filtering Excel 2003 Charts to a date range
I have multiple charts that we look for trends on over 1-2 year time frames.
Is it possible to have a chart show all the data that has been entered and
when looking at the chart have a date range data to format that range
differently. What I am trying to do is we have weekly or montly meetings, in
these meetings I would like to show all the additional data differently that
has been added since our last meeting, while showing accumulative data in a
The easiest way to get different formats is to use different series. If you
had one series, you could use autofiltering to...Multiple Cell Formats
Is it possible for a column to have more than one format, but for those
formats to be similar.
I would like to have a multi-format date column. The format can either be
Month-Year (Jan-99) or Year only (1999). (I would even like to have
something like Jan/Feb-99, but I can live without that.)
Any help would be appreciated.
With true Excel dates in column A e.g. 01 Feb 07, in column B
=A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07
=A1 Format>Cells>Number>Custom> yyyy will give 2007
or =TEXT(A1,"yy...Line Color on Format Autoshape (arrows) won't hold
I'm using arrows from the Drawing toolbox and the color is on automatic
and the arrow line keeps disappearing. I've changed it from automatic
to a fixed color and it won't hold either. WTF?
Any help will be appreciated.
...highlight cell with colour if date overdue
can anyone tell me please how do i highlight a cell in a spreadsheet with
colour if date overdue
And how exactly should XL determine that the date was overdue?
Take a look at Conditional Formatting in XL Help.
If, for instance, the due date is in A1 and the date is overdue if it's
in the past, select A1, choose Format/Conditional Formatting... and use
In article <3B7B11E7-894B-458A-BDE6-7710E0743D31@microsoft.com>,
Harvey <Harvey@discussions.microsoft.com> wrote:
> can anyone ...Invalid Argument Error when using 1/1/08 as a date
Not sure if this is a known issue - can anyone else create a record that
contains a date field with the value of 1/1/08 or 1/1/07, save it and then
open it again without getting an "Invalid Argument" error. I am able to
produce this error across different environments, different entities and
different date fields.
Would be interesting to hear more about this.
Just to let you know, I just created a task in CRM 4 and set the due date to
1/1/08 and it worked without any issues. This is a field displayed as a
Date/Time box, on not just Date. I don...Formatting with CONCATENATE
I would like to use the TODAY() function with a leading word, in this case
"Per ". The formula
="Per "&TODAY() produces "Per 38238" and I can't see any way to format the
"38238" to make today's date readable. It's easy to do it by using 2
columns, one for "Per" and one for the date, but is there any way to do this
in one column?
Try some variation of the following:
>I would like to use the TODAY() function with a leading
word, in...Conditional Formatting Text!!
I want to conditionally format some text in a spreadsheet using
I have column A1 with:
What I want to do is for all cells which contain "5a Be" "5b Be" 5c
Be" "4a Be" "4b be" and "4c Be" to be highlighted in Red - is there a
formula to put all of them into one formula - If("5a Be" or "5b Be)
"Kiran" <firstname.lastname@example.org> wrote in message
...Date Formatting Problem #6
How can I convert the following date format into something useable that
excel can recognize as a vaild date?
Here is the date format I have to work with:
Any help would be much appreciated. Thank you!
Mark your range of data. Data>Text to Columns>Next>Next>Click Radio button
for choose one of the date formats to suit.
"Jocko69" <Jocko69.email@example.com> wrote in message
> How can I convert the following date f...Conditional formating using VBA
I have to format cells (I4:J37) if cells (I43:J76) are:
How do I write that in VBA? (I am new to VBA so any help would be appreciated)
I asume your users will enter a value in the range("I43:J76").
In that case you case use the Worksheet_change(Byval Target as Range
function like so:
Private Sub Worksheet_Change(ByVal Target As Range)
' Check if only 1 cells value is changed
If Target.Cells.Count > 1 Then Exit Sub
' Check if changed c...Converting current date to Julian Date
Does anyone know a simple function for converting the current date to a
julian date. So Jan 1 would be 1 and Dec 31 would be 365 (except in leap
year, of course).
Sr. Software Engineer
Thats not really julian date.
But if you want the day of year, use COleDateTime::GetDayOfYear().
If you are really looking for julian date which is the number of days since
January 1, 4713 BC and today is 2453416.14309
here is a site with a formula to calucate it.