2

How would I format a date in a table such that the next row cannot have a date that precedes the current row?

For example, I have a table of 2 columns which have column headings "Date" and "Employee" and my first row (not the heading row) has the date as 20/10/2016. How do I ensure the next row cannot have a date that precedes 20/10/2016? (i.e. the next row's date must be the same or a date after 20/10/2016).

Thus if I have three rows of date values as 20/10/2016 and I start a new date 21/10/2016 then every succeeding row must be greater than or equal to 21/10/2016

Improve this question

2 Answers 2

Reset to default
3

Well you tagged it as data validation, so why not use that? Data tab -> Data validation -> enter image description here

A2 being the first value in the list, and this being the data validation for A3. Copying the cell to A4, will automatically change the validation to =A3.

Improve this answer
10
  • Well in my case it will be 'greater than or equal to' for the data tab. Can this be implemented for a table i.e. if I have a start date value in a cell on another sheet and I carry out the data validation for the first row under column date in my 3 column table for instance and I append my table by either writing in the row cell below date column and hitting enter or by expanding it using the invetered L at the end of each table does it automatically copy that validation to the next row?
    – MrMarho
    Commented Oct 22, 2016 at 16:07
  • It doesn't work just tried it. It must be the cell above. Unless you have a way to bypass this?
    – MrMarho
    Commented Oct 22, 2016 at 16:15
  • Well, it depends on exactly how you want it to work, but if you have the start date on anything but the 1st. row, the code will as your noticed still continue down from the start date. You can keep that from happening by changing from say "=Sheet2!A1" to an absolute reference "=Sheet2!$A$1". The problem with this however, is that now all rows will only be limited by the start date. Another thing you could do, is to point the first row to the start date, and then use my example starting the second row. Downside would be if you delete the table rows, you'd have to set that validation again.
    – Christofer Weber
    Commented Oct 22, 2016 at 19:37
  • @MrMarho Ok, so I think I found a special solution to your problem. However, if you expand the table using the "inverted L" the data validation won't copy, then you'd need some kind of VBA. Ok, so here is what I did: Say that your start date resides on sheet 2, cell H3. And your table with a text header is on sheet 1 column Q, starting with the header at Q3. In Q4, add a custom data validation, not date. and enter =AND(Sheet2!$H$3<=Q4;IF(CELL("format";Q3)="D1";Q3;Q4)<=Q4) Change accordingly to your sheet. Make sure all date cells are formatted as "date".
    – Christofer Weber
    Commented Oct 22, 2016 at 21:27
  • If I understand your formula well this bit IF(CELL("format";Q3)="D1";Q3;Q4) says is cell format of Q3 is dd-mm-yyyy then output Q3 else Q4,
    – MrMarho
    Commented Oct 23, 2016 at 22:01
0 
=TEXT(A1,"mm/dd/yyyy")

Eg to construct a SQL statement:

="insert into qualification (created_date) VALUES ('" & TEXT(Q3,"dd/mm/yyyy") & "')"
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .