formula get specific date based on date of computer

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
hello
i need formula to specific date after calculate 12 months so i have date in column a and the result in column b so it a2 = 1/7/2019 and when add 12 months when date reaches before finish day it shows in b2 =1/7/2020 it supposes showing this date when date of computer becomes 30/06/2020 i would this formula is dynamic because i have many dates
input

1 .xlsm
ABCD
1CURRENT DAYA NEW DATE
201/07/2019
315/05/2018
410/01/2011
5
6
sheet3

output
1 .xlsm
ABCDE
1CURRENT DAYA NEW DATE
201/07/201901/07/2020
315/05/201815/05/2019
410/12/201110/12/2011
5
6
sheet3
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
thanks steve but not exactly i would the result in b2 when the date of computer remains one day to reach end of date for instance
the date of computer should 30/06/2020 then the date in b2= 1/07/2020
 
Upvote 0
Book2
AB
1CURRENT DAYA NEW DATE
225-5-201925-5-2020
326-5-201926-5-2020
427-5-2019-
528-5-2019-
629-5-2019-
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=IF(TODAY()>=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1, DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)), "-")
 
Upvote 0
hi, Gwteb yes it works but i would adjusting can you make formula b2= - no date if the date of computer missed the date for instance
if a2= 1/07/2019 and the date of computer = 2/07/2020 then b2= - no date
 
Upvote 0
if a2= 1/07/2019 and the date of computer = 2/07/2020 then b2= - no date
No, it's not, the formula takes that into account. See for yourself:
Book2
AB
1CURRENT DAYA NEW DATE
223-5-201923-5-2020
324-5-201924-5-2020
425-5-201925-5-2020
526-5-201926-5-2020
627-5-2019-
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=IF(TODAY()>=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1, DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)), "-")
 
Upvote 0
How about
=IF(AND(TODAY()-1<=EDATE(A2,12),TODAY()+1>=EDATE(A2,12)),EDATE(A2,12),"")
 
Upvote 0
Gwteb it doesn't work when the date computer reaches 2/07/2020 it supposes no date in b2
 
Upvote 0
hi, fluff your formula works perfectly thanks so much but i have somthing is upsetting i have to press enter twice on cell b2 to change
 
Upvote 0
Gwteb it doesn't work when the date computer reaches 2/07/2020 it supposes no date in b2
Now I'm sort of confused. Did you alter the computer date for testing? It should give you what you want as of your post #1
when date reaches before finish day it shows in b2 =1/7/2020 it supposes showing this date when date of computer becomes 30/06/2020 i would this formula is dynamic
Book2
ABC
1CURRENT DAYGWteBFluff
221-5-201921-5-2020 
322-5-201922-5-2020 
423-5-201923-5-2020 
524-5-201924-5-2020 
625-5-201925-5-202025-5-2020
726-5-201926-5-202026-5-2020
827-5-201927-5-202027-5-2020
928-5-2019- 
1029-5-2019- 
1130-5-2019- 
1231-5-2019- 
131-6-2019- 
142-6-2019- 
153-6-2019- 
164-6-2019- 
175-6-2019- 
186-6-2019- 
197-6-2019- 
208-6-2019- 
219-6-2019- 
2210-6-2019- 
2311-6-2019- 
Sheet1
Cell Formulas
RangeFormula
B2:B23B2=IF(TODAY()>=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1, DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)), "-")
C2:C23C2=IF(AND(TODAY()-1<=EDATE(A2,12),TODAY()+1>=EDATE(A2,12)),EDATE(A2,12),"")


EDIT: Note that where I live it's May 26th today.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,784
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top