LinkBack URL
About LinkBacks
Hi. The current formula that I have can only calculate a date that is 5 months ahead of the cell date:
=DATE(YEAR(A1),MONTH(A1)+5,DAY(A1))
Is there a way to modify the formula to calculate a date that is 5 months ahead of the computer's system time?
Hi. The current formula that I have can only calculate a date that is 5 months ahead of the cell date:
=DATE(YEAR(A1),MONTH(A1)+5,DAY(A1))
Is there a way to modify the formula to calculate a date that is 5 months ahead of the computer's system time?
You can use the TODAY() function..
So in A1, put
=TODAY()
Then the formula you just posted now refers to the current date according to the computer.
Thanks, that worked!
Is there a way to calculate 5 months ahead of the computer's system time without specifying it?
Depending what you understand by "5 months ahead" [what's 5 months ahead of 30th September 2013?] you might want to use EDATE
=EDATE(TODAY(),5)
Audere est facere
When I used that formula in a random cell, the result was 41721. Is that formula not able to calculate a date?
41721 is how excel stores dates (it's the number of days since 1st Jan 1900) - you need to format the cell in a valid date format
Right click > format cells > number > date > choose a format like m/d/yyyy
Thanks. Is there a way to modify that formula to calculate 5 calendar months from today?
Ex:
5 months from today (10/23/13) is 3/23/14.
5 calendar months from today (10/23/13) is 3/1/14.
You mean to always give 1st of the month? Perhaps try thisOriginally Posted by jhudson444
=EOMONTH(TODAY(),4)+1
That will give you 1st March 2014 today, come 1st November that will change to 1st April.....
Thanks!
Is there a way to modify that formula (or create a new one) which will give me the 1st of the month that is 5 months ahead of a range of cells that has less than 2 words?
Ex.
.....A..........B
4/1/13......excel
6/4/13......excel
7/2/13............
The range between 6/4/13 and 7/2/13 has only one word, so 5 months (with the first of the month) ahead of 7/2/13 is 12/1/13. The formula would be based on the criteria of "less than 2 words" in column B.
Last edited by jhudson444; 10-23-2013 at 06:29 PM.
The following is what I have so far:
This formula counts 5 calendar months ahead of 7/2/13:
=EOMONTH(A3,4)+1
This formula counts the number of "excel" words:
=SUMPRODUCT((LEN(B1:B3)-LEN(SUBSTITUTE(B1:B3,"excel","")))/LEN("excel"))
This formula counts the number of "excel" words in the past 2 calendar months based on today's date:
=SUMPRODUCT((LEN(B1:B3)-LEN(SUBSTITUTE(B1:B3,"excel","")))*(A1:A3>=EDATE(TODAY()-DAY(TODAY())+1,-2)))/LEN("excel")
Is it possible to combine these formulas (or similar ones) to calculate a date when there will be less than two "excel" words?
Last edited by jhudson444; 10-23-2013 at 07:13 PM.
I can upload a workbook with my current formulas if anyone wants.
To help clarify for anyone who is currently trying to help me out, the formula logic should be something like:
"If a range of cells has [criteria 1], then [criteria 2]"
Criteria 1: Less than 2 words
Criteria 2: Add 5 months to the most-recent cell which does not meet criteria 1
Hopefully this helps to clarify the problem.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks