Chronological Date Orders

Hi

I have a worksheet column (L6:L34) into which users are asked to enter dates 
that payments are made.

The columns are formatted as 'Date' and Data Validation is set to ensure a 
date is entered.  A blank entry is allowed.

Initially all the cells are blank and the user is expected to populate the 
cells, starting with the earliest date in L6 and moving sequentially down 
the column.  Of course, there's no guarantee it will happen that way.

It doesn't matter if a cell is skipped (left blank), but I would like to 
ensure that any dates that are entered, are entered in chronological order, 
with the earliest date at the top.

I guess a simple message alert in a cell outside the print area would be 
sufficient in the event someone gets the date order wrong.

Can anyone provide me with a formula that would look at the column entries 
and trigger the alert if the dates that have been entered are out of 
chronological order?

The formula would have to ignore blank cells (including when the entire 
column is blank), and a single cell entry (when only one cell has a date 
entered).

TIA
-- 
Mike
-Please remove 'safetycatch' from email address before firing off your 
reply- 


0
3/31/2008 9:24:13 AM
excel 39879 articles. 2 followers. Follow

2 Replies
615 Views

Similar Articles

[PageSpeed] 31

Hi Mike,

Maybe a Conditional Format.
Select L6:L34
And apply CF with
Formula is:  =AND(L6<>"",L6<MAX($L$6:L6))

HTH
Martin



"mlv" <mike.safetycatchvincent@jet.co.uk> wrote in message 
news:fsqaju$93s$1@north.jnrs.ja.net...
> Hi
>
> I have a worksheet column (L6:L34) into which users are asked to enter 
> dates that payments are made.
>
> The columns are formatted as 'Date' and Data Validation is set to ensure a 
> date is entered.  A blank entry is allowed.
>
> Initially all the cells are blank and the user is expected to populate the 
> cells, starting with the earliest date in L6 and moving sequentially down 
> the column.  Of course, there's no guarantee it will happen that way.
>
> It doesn't matter if a cell is skipped (left blank), but I would like to 
> ensure that any dates that are entered, are entered in chronological 
> order, with the earliest date at the top.
>
> I guess a simple message alert in a cell outside the print area would be 
> sufficient in the event someone gets the date order wrong.
>
> Can anyone provide me with a formula that would look at the column entries 
> and trigger the alert if the dates that have been entered are out of 
> chronological order?
>
> The formula would have to ignore blank cells (including when the entire 
> column is blank), and a single cell entry (when only one cell has a date 
> entered).
>
> TIA
> -- 
> Mike
> -Please remove 'safetycatch' from email address before firing off your 
> reply-
> 


0
mtmw (348)
3/31/2008 10:16:06 AM
Martin Wrote:
>
> Maybe a Conditional Format.
> Select L6:L34
> And apply CF with
> Formula is:  =AND(L6<>"",L6<MAX($L$6:L6))

Hi Martin

I had considered using conditional formatting, but couldn't see how I could 
use CF to bring up the alert message.

With CF I could highlight the non-chronological cell entry by changing the 
font and/or background colour, but I can't see how to convey to the user 
what the problem is.  Even if I could use CF to change the text in the cell, 
the cell isn't large enough to carry the alert message.

Maybe I have to use a helper column to check each L6:L34 entry, and then use 
the helper column to initiate the alert message if a date is entered that is 
not in chronological order.

Something like:

=IF(AND(L6<>"",L6<MAX($L$6:L6)),1,"")  Copied through (say) cells Z6:Z34

Then, in the message alert cell:

=IF(SUM(Z6:Z34)>0,"ERROR - Dates are not in chronological order","")

It's rather a convoluted approach, and I haven't tested the formula yet to 
prove it works, but the principle is there, hopefully.

Maybe there is a shorter route?
-- 
Mike
-Please remove 'safetycatch' from email address before firing off your 
reply-




0
3/31/2008 2:52:50 PM
Reply:

Similar Artilces:

Days in Text to Dates SPECIAL
Through great effort, I have sorted and averaged the patient demand for appointments over the last three years. For instance, now I know how many patients to expect on the "3rd Tuesday in November". Is there a way to tell MS Excel to present to me the date in FY10 which corresponds to the concept "3rd Tuesday in November"? DOUG ECKERT in WICHITA KS PS, I have resorted to filtering a list of dates and a list of "X DAYS OF THE YEAR WITH X AMOUNT OF DEMAND ON THIS DATE" and pasting the actual dates to the corresponding rows where I need them one-by-on...

Dates that automatically update ...
Can anyone help me? What I want to do, is have excel update a timesheets pay period dates automatically. Is this possible? For example, On a specific date, I want the pay period dates to change to the next pay period dates on 4 different sheets in the same excel document. How do I do that? If that is not possible, how can I change ONE date to change the rest of them on all 4 sheets? It is very time consuming to do it all manually. Your help is appreciated. Thank you! I suspect it is possible to do the first part, but I'd have to think about it to get to a solution. You...

Tab Key order
Please tell me how to set up my tab key so that it goes to the next adjacent cell, right now it goes from cell A1 to L1 for example. <Tools> <Options> <Transition> tab, And *uncheck* "TransitionNavigationKeys". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "CarolL" <carol_lapointe@hotmail.com> wrote in message news:ABA7A007-3DDC-4CA6-ABEB-1A376D0591FE@microsoft.com... Please tell me how to set up my tab key so that it ...

Current date and time on the report
Is there any control I can put onto the report showing the current date and time of printing? Hi Create a text box on the report and set it's control source property to the below: =now() Regards On 7 Jun, 08:30, "Alan T" <alanpltseNOS...@yahoo.com.au> wrote: > Is there any control I can put onto the report showing the current date and > time of printing? ...

Invoice report NOT showing the back order quantity field-but it sh
We recently upgraded to DGPv10 and our accounting department has noticed that the back ordered items are not showing on the invoices, only the items that have been fulfilled. I looked at the invoice report and it is set up to show/include the BACK ORDER QUANTITY FIELD. How can I get this field to actually SHOW on the invoice? What might we be doing wrong. It has been this way since we upgraded from v9 - v10 this past summer and the lack of the backorderd - material showing is haunting me. Any help would be appreciated. When you fulfill the orders, do you place the remain...

How do I get year to date earnings on a payroll spreadsheet(what'.
I am trying to create a payroll spreadsheet. I want to get the formula for year to date. I would rather not use a 52 column sheet with totals. How can I add the change each week without using a large sheet? If I can offer an opinion: regarding payroll, keep as much information as you can. Payroll is sacred- people literally live by it, and they are VERY concerned that proper records are kept. A Fortune 500 company is judged by its stock price; small and even medium sized companies are judged by whether or not they met payroll. My suggestion is to allow the spreadsheet to be as large as i...

Anyone Else Want "as of" date for receiving POs?
The whole COGS system in RMS does not make sense. The fact that the COGS is permanently recorded in the database at the time of the sale does not take into account timing issues that occur in the normal course of business. For example, quite frequently I am unable to receive and commit items to the database the second they physically arrive (either due to time constraints or because I have not received the invoice from the supplier or know all of my costs yet, so I do not know the exact cost value yet). However, if it is physically in stock, I want to sell it if a customer asks, even if...

Convert a Date to Character
How do I Convert a Date to Character in MsAccess ? I'm an old Foxpro user and I know there was a command called - DTOC ( date to character) Could anyone tell me the equivalent in MsAccess? Use CStr(varTest) -- BrerGoose Moose-Tech Software: http://ourworld.compuserve.com/homepages/webspace/MTS/Home.htm I appreciate this very much Thanks a million "BrerGoose" <BrerGoose@discussions.microsoft.com> wrote in message news:29FBB8F7-75CE-4FE1-BED2-7C7ED9426B69@microsoft.com... > Use CStr(varTest) > -- > BrerGoose > Moose-Tech Software: > http://ou...

Forwarding e-mails / Time & date stamp on e-mails
I have 2 questions regarding e-mails created within CRM (web client): 1) How can I verify my e-mail in CRM was sent? 2) Is there the ability to forward and/or re-send an e-mail previously sent within CRM? Denise, You should be able to back to original item you have found and select Actions-> Forward, select the contact you wish to send to and cilck Send. You must ensure you have the appropriate permissions to do so. Habib "Denise" wrote: > I have 2 questions regarding e-mails created within CRM (web client): 1) How > can I verify my e-mail in CRM was sent? 2) Is th...

Combined date time cell to separate date & time components #2
From file dump have combined date time cells eg 14/04/03 14:20 (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) See the reply to your post in the microsoft.public.excel newsgroup. Please don't post the same question to multiple groups. It just wastes the time of those replying to a question that was...

Special charaters are displayed in inverted order by CTreeCtrl
I build a file browser like tree with CTreeCtrl. Now, if the control has to display a drive like "C:", it instead displays ":C". The same happens with "Program Files (x86)" which is displayed as ")Program Files (x86". So, it seems that if the the last character is a special character, it becomes the first character in display name. Is this a unicode problem? Anyway, very strange. Here is my coding: CFileSystem fs; wchar_t d[3]; LPTVINSERTSTRUCTW itemstruc = NULL; CString text; //Load static images m_FileViewImages.Cre...

Counting Dates..is it possible?
I'm working on a health project and I want to count the number of people who completed their Baseline paperwork, but did not get randomized into a treatment group because they dropped out of the study. I want to refer to Baseline Date (Date) and Randomized (Yes? No?) If column D2:D999 (Baseline Date) have a date in the cell, then count it. PLUS If column E2:E999 (Randomized) is "No" then count it. I tried =SUM((COUNTIF(Enrollment!D2:D499,"(DATE)")))+(COUNTIF(Enrollment!E2:E499,"No")) but this formulas is counting anything in D2:D499 I do not want to u...

date formulas #2
I have designed a excel invoice for a small busines I run. One of the formula's I have used is =Now()which puts the current date on the spreadsheet. Where my problem occurs is, if several months later I am asked by a customer for a dulicate copy of the invoice and I retreive the invoice the current date is put in. Question, is there anyway I can retreive an invoice and still maintain the original date. Doug Doug, One suggestion: When you save the original invoice do a Copy, Paste Special|Values on the cell that contains the invoice date. HTH, Fred >-----Original Message----- &g...

[Plaxo Contacts] Getting up-to-date
I got a problem. First, sorry for my scholastic English, but i live in Italy. I didn't know Plaxo Contacts. A friend of mine sent me the "getting up-to-date" email in which i could write my informations. He sent me an HTML-based email with, on the right, my "card", ready to be filled. I reply. Then, I downloaded Plaxo and requested the updating infos to my contacts... why did i send a normal-text mail instead of the HTML-based with card as my first friend did? The second method seems to me more simple than the other: don't you think? How did my friend of mine send ...

Date format #2
Hi, I want to write a formula, such that everytime Column B is a Monday the Date in Column C be copied over from the previous Friday . (Sep 8 Mon Sep 5) A B C 05-Sep-03 Fri 29.Aug 08-Sep-03 Mon 30.Aug 09-Sep-03 Tues 31.Aug 10-Sep-03 Wed 01.Sep 11-Sep-03 Thurs 02.Sep 12-Sep-03 Fri 03.Sep 15-Sep-03 Mon 04.Sep 16-Sep-03 Tues 05.Sep Thx Omer Simple solution with the following assumptions: Assumption 1: Column A contains dates (and not text formatted data looking like dates) Assumption 2: Column A-B contain chronological and all dates (excluding weekends). T...

Sales Order Processing Comments
Hi, i am newbie here in Great Plains, i want to ask about sales order processing comments, what it's means about and where i can fill about sales order processing comments. I need it because it's must be filled for integration order with CRM. Thx before ^_^ ...

grouping by dates in pivottable
Hi all, My dataset contains a column of start dates / times formatted as dd/mm/yyyy hh:mm I have created a pivot table with this field as the column headers. I would like to group all the data by Month and year so that my column headers would read: Jun 05 Jul 05 Aug 05 and then the data beneath these headings would be the total occurances in the month. I have done grouping in pivottables before but not with dates and I keep getting an error box saying "Cannot group selection". I am getting :mad: and :confused: trying to find out what the problem is cos I know that thi...

Find Next Available Date
I have a query that filters out weekends and holiday dates. This query drives a combo box on my form. I would like the default value in this combo box to be the next available maturity date from the filtered query. The next available date will be driven off of a text box "trade_date" that defaults to date(). I have been struggling with this one. Any ideas? Nate, There might be a simpler way suggested by one of our experts, but I like to deal with dates using values based on the current default epoch in Access. That said, it's fairly straight forward in code to find the curr...

Date calculations #2
I'm looking for a way to calculate if a date is between to other dates then one thing, if not another thing. The situation is I rent a cabin year round. The pricing between Jan. 1 and May 25 is one Price, the rest of the time the Pricing is another . Gourd and Bob I knew I'd be back soon... LOL On Wed, 17 Oct 2007 08:03:01 -0500, "Barry" <candeman55@hotmail.com> wrote: >I'm looking for a way to calculate if a date is between to other dates then >one thing, if not another thing. > >The situation is I rent a cabin year round. The pricing between ...

Date Formats in Excel are incorrect
have created an app in ASP which prints out timeclock events. It also saves them to an excel file. Problem is the dates are displayed correctly on screen and in the printout, however, any date in 2004 is displayed as mm/dd/yyyy. All dates for 2003 were displayed as per my locale dd/mm/yyyy. Changing the following code to use long date format has been the only solution so far, but I need to use short date format of dd/mm/yyyy. Response.write "objXLSheet.Range(""" & strColumnVal & intCounter & """).NumberFormat = ""dd/mm/yyyy""...

Date Format 01-07-08
How can I format a date field so the result shows up as January 1, 2008 (month spelled out)? I'm using Access 2007 and the date field will be merged into a Word document, which is where it actually needs to have the month spelled out. -- Thanks in advance for your time! denilynn Create a query, and use the Format function (not the Format property) in the query (with a format of mmmm d, yyyy). Export the query, rather than the table. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "denilynn" <denilynn@discussions.microsoft.com> wr...

Current date in cell
How do I put a current date in cell, meaning a formula so that when I open the spreadsheet the cell will have todays date in it. 4th July 2009 thanks =today() in the cell but it will change with each day and calculate with any other calculation. Or, put in the ThisWorkbook module in the workbook_open event sheets("yoursheetnamehere").range("yourrnage")=date -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "SS" <nonense50@blueyonder.co.uk> wrote in message news:QaP3m.46662$Eq6.15411@newsfe24.ams2... > How do I pu...

Date Formats
Hi I'm having trouble writing the right date format to a cell. Currently I have tried Worksheets("Forecast").Cells(ForecastMaxRowNo, 8).Value = Format(Date, "Short Date") Worksheets("Forecast").Cells(ForecastMaxRowNo, 8).Value = Format(Date, "General Date") Worksheets(sMonth).Cells(MonthTabMaxRowNo, 8).Value = FormatDateTime(Date, vbShortDate) Each time it comes out in American format and my regional setting on the computer are all set to UK When I put MSGBOX before it to test it seems to be OK. The Cell format seems to be...

Getting FileSystem Date
I found the code below to read a directory and list the filenames and bytes into a worksheet. This is great but I also need the file system date for each file. For the life of me I cannot find the correct property to use. Here's the code I have that essentially won't give me the file date: Sub GetAttributes() On Error Resume Next Dim FolderPath 'Path to search for files Dim objFSO 'fileSystemObject Dim objFolder 'folder object Dim colFiles 'Collection of files from files method Dim objFile 'individual file object Dim ws Dim x Fold...

Converting Dates after Import
I have imported employee data from an AS400. The text for dates import as "1012008" for 01/01/08 or 12012008 for 12/01/08. Is it possible to write a formula to insert the "/" for 4 spaces from the left and again at 6 spaces from the left. I need to be able to make these usable dates without speinding a lot of time. Other suggestions for converting these to a workable date would be appreciated. Thank you for your help. Data/ Text to Colums/ Delimited/ Next/ Next/ Date : MDY -- Gary''s Student - gsnu200829 "Still learning@work" wrote: > I ...