Formula to check date entries

  • Thread starter Thread starter PZ
  • Start date Start date
P

PZ

Spreadsheet keeps track of date entries as tasks are completed for an
activity in a row.

Looking for a formula to put in a column to check to make sure dates
entered are in chronological order.

Example

Record1

Start Date is in A1
Stage 1 Date is in C1
Stage 2 Date is id E1
End Date is in M1

formula would check to make sure the dates entered did not precede any
of the previous activitiy's dates.

This formula works but it shows true when the cells are blank as well,
plus it gets lengthy the more activity dates you add and you have to
replicate the string for each date cell.

=(a1<>"")*Or((c1-a1)<0,(e1-a1)<0,(m1-a1)<0)
 
How about something like:

=IF(AND(A1<C1, C1<E1, E1<M1),"good","bad")

You can replace good and bad with whatever you want to show in the cells
if they are or are not in order (including nothing if you don't want
anything to show).

--David
--
David M. Marcovitz
Microsoft PowerPoint MVP
Director of Graduate Programs in Educational Technology
Loyola College in Maryland
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/
 
=AND(A1<=IF(C1="",A1,C1),C1<=IF(E1="",C1,E1),E1<=IF(M1="",E1,M1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Instead of using a formula, why not consider Conditional Formatting. Try
this and see if you like it as a solution. Click on the C in column C's
header in order to select the entire column (and make C1 the active cell).
Right-click the selection and select Conditional Formatting from the list
that appears. Click the Add>> button because we are going to set up two
separate conditions. Select "Formula Is" from the drop down box on each
condition and set the following for each...

Condition 1
==============
Formula: =AND(C1<>"",NOT(ISNUMBER(C1)))
Format: Pick the red color from the Pattern Tab

Condition 2
==============
Formula: ==C1<A1
Format: Pick the pink color from the Pattern Tab

Do the same thing for column E, but change the cell reference C1 to E1 and
A1 to C1. Then repeat this for column M, changing the references
accordingly.

Now, when a bad date is typed or pasted in, the cell will turn red; and if a
date that is earlier than the previous column is typed or pasted in, the
cell will turn pink.

Rick
 
Spreadsheet keeps track of date entries as tasks are completed for an
activity in a row.

Looking for a formula to put in a column to check to make sure dates
entered are in chronological order.

Example

Record1

Start Date is in A1
Stage 1 Date is in C1
Stage 2 Date is id E1
End Date is in M1

formula would check to make sure the dates entered did not precede any
of the previous activitiy's dates.

This formula works but it shows true when the cells are blank as well,
plus it gets lengthy the more activity dates you add and you have to
replicate the string for each date cell.

=(a1<>"")*Or((c1-a1)<0,(e1-a1)<0,(m1-a1)<0)
Click to expand...

In addition to what others have recommended, consider using Data Validation to
ensure that out of order dates cannot be entered.
--ron
 
Back
Top