Date macro or formula

[Fraserj]

I have no experience of excel formulas but want to create one for work with information moving to a 2nd sheet if its been on for over two weeks, so that we still have information surrounding it.

Can anybody please help me with this?

Information	Date
Bla bla bla	03.03.2023

 

[awoohaw]

With excel formulas you cannot really move data. Your represent it in other cells using formulas. So, to that end you cannot move rows from a sheet to another by using a formula. But, you can identify what cells to select using autofilter, and cut and paste them into a new worksheet.
So, for the formula you want to use, you would put the reference date in a cell on the top row of an unused column adjacent to your data.
the twos weeks prior formula would be something like this, assuming the record date is in cell B2, and the date check column is column C. The reference date is in cell C1. You would enter the following formula in cell C2 and copy down:

=B2 + 14 < C2

This will give you a TRUE/FALSE result Then Filter.

PS. YOu do not show what version of excel you use. Sometimes solutions are non existent or more difficult in earlier versions and it helps the forum help you. Using the xl2bb add in (link below) also allows you (and responders) to post mini workbooks that can be pasted into their own machines.

 

[Fraserj]

Thank you, didn't think of that.

For an archive page

Is there a way of saying if B4= False
The row that says false on it is copied onto the archive sheet?

 

[Fraserj]

With excel formulas you cannot really move data. Your represent it in other cells using formulas. So, to that end you cannot move rows from a sheet to another by using a formula. But, you can identify what cells to select using autofilter, and cut and paste them into a new worksheet.
So, for the formula you want to use, you would put the reference date in a cell on the top row of an unused column adjacent to your data.
the twos weeks prior formula would be something like this, assuming the record date is in cell B2, and the date check column is column C. The reference date is in cell C1. You would enter the following formula in cell C2 and copy down:

=B2 + 14 < C2

This will give you a TRUE/FALSE result Then Filter.

PS. YOu do not show what version of excel you use. Sometimes solutions are non existent or more difficult in earlier versions and it helps the forum help you. Using the xl2bb add in (link below) also allows you (and responders) to post mini workbooks that can be pasted into their own machines.

Thank you, didn't think of that.

For an archive page

Is there a way of saying if B4= False
The row that says false on it is copied onto the archive sheet?

 

[awoohaw]

There is not need to put a formula in anohter column that is just B4=FALSE... just filter on Column B for the cells that are FALSE

 

[Fraserj]

There is not need to put a formula in anohter column that is just B4=FALSE... just filter on Column B for the cells that are FALSE

It won't just be me using it and the people who will be are less useful than me.

Every answer is coming up with false, how do I calculate if the date has gone past 2 weeks?
At the moment I don't have a cell that says True so it can be filtered.

Version is Microsoft 365 for enterprise
Version 2302

Dunno if that helps?

 

[awoohaw]

Column B in the example below uses the formula I gave above. Just filter on that, selecting all TRUES for greater than 14 days, and FALSES for less than 14 days.
You underestimate your coworkers. This is not difficult. But to make it more descriptive you could use an if STATEMENT.

= IF(A2+14>B$1,"LATE","NOT LATE")

Book1
	A	B
1		Sunday, March 19, 2023
2	Wednesday, March 1, 2023	FALSE
3	Monday, March 6, 2023	TRUE
4	Thursday, March 2, 2023	FALSE
5	Saturday, March 4, 2023	FALSE
6	Friday, March 10, 2023	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=A2+14>B$1

 

[Fraserj]

Column B in the example below uses the formula I gave above. Just filter on that, selecting all TRUES for greater than 14 days, and FALSES for less than 14 days.
You underestimate your coworkers. This is not difficult. But to make it more descriptive you could use an if STATEMENT.

= IF(A2+14>B$1,"LATE","NOT LATE")

Book1
	A	B
1		Sunday, March 19, 2023
2	Wednesday, March 1, 2023	FALSE
3	Monday, March 6, 2023	TRUE
4	Thursday, March 2, 2023	FALSE
5	Saturday, March 4, 2023	FALSE
6	Friday, March 10, 2023	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=A2+14>B$1

All looking good, my only issue is, if I filter this to only show false.... The formula I have put in disappears therefore cannot add any more information. Example I'll drag the b1+14 formula down to cell 1000. Mean once the filter is on I can only type in 1001 and there isn't a formula.

Is there a way of if there is no date, this will stay blank.

I'm unsure whether I'm explaining it very well.

 

[awoohaw]

filtering does not delete formulas, I'm unsure of what you mean.
But, let me try to think .... are you saying when you cut and paste the "FALSE" records to another worksheet there are no more records on the original worksheet?
If that is the case all the records were equal to FALSE and it makes sense you would take all the formulas away to the other worksheet.


Regarding "Is there a way of if there is no date, this will stay blank." you can wrap the formula in another IF statement.

=IF(A2 = "", IF(A2+14>B$1,"Late","Not Late"))

^^ This formula works if the cell is completely empty or if there is a formula that calculates to a "".

 

[Fraserj]

filtering does not delete formulas, I'm unsure of what you mean.
But, let me try to think .... are you saying when you cut and paste the "FALSE" records to another worksheet there are no more records on the original worksheet?
If that is the case all the records were equal to FALSE and it makes sense you would take all the formulas away to the other worksheet.


Regarding "Is there a way of if there is no date, this will stay blank." you can wrap the formula in another IF statement.

=IF(A2 = "", IF(A2+14>B$1,"Late","Not Late"))

^^ This formula works if the cell is completely empty or if there is a formula that calculates to a "".