Forum Discussion
Can I make a cell value appear only after a certain date?
Hello, I'm a beginner here. Is there a formula or function I can use (perhaps a =IF?) where I can make a certain formula's value appear in a cell only after a certain date?
Context: on each month's sheet of my annual budget spreadsheet, I have a cell showing me that month's income minus expenses. On a separate summary sheet for the whole year, I would like to see that value for the month only after the month is over (ie, it only shows me June's income - expenses on or after July 1st).
Hope that makes sense. Thanks.
Sorry, I forgot to correct the reference when copy/paste your initial formula.
=IF(TODAY()>=DATEVALUE("07/01/2018"), Jun!V24, "")
35 Replies
- This discussion really helped my issue. I calculate on a specific date but once the date passes the formula wipes out my information. How do I get the formula to function on one day and retain that information once the date passes.
=IF(TODAY()=DATEVALUE("2023-02-07"),INDEX(PLAYING!$E$1:$G$200,MATCH(DE4,PLAYING!$E$1:PLAYING!$E$200,0),2))
TEAM # GROSS HDCP 14 L
ACOBA, JEFF 25 11 14 L
formula resides in cell and looks at ACOBA, JEFF and finds his handicap (11) and populates. I want this to populate on correct date but once that date passes I want the information to stay and not change. Help please.James_Pape I was able to work with a colleague and he helped me find the way. It appears two calendar days after the date in the top of the column. you can adjust it to not have the "+2". It appears with the date you indicate and stays there.
"=IF(TODAY()>=(N$2+2),VLOOKUP(N$2,'H:\" the rest of my formula is after the H. but the part you need is, "=IF(TODAY()>=(N$2+2)," the N2 cell is where i have the current date, in row 2.
I hope I understood your question and this helps you.
Thank You for your efforts. But, still have an issue.
I understand your "=IF(TODAY()>=(N$2+2)"
=IF(TODAY()=(DD$2+2),INDEX(PLAYING!$E$1:$G$200,MATCH(DE4,PLAYING!$E$1:PLAYING!$E$200,0),2))
This does calculate my cell two days prior to current date which I can work with, but, once the date has passed the formula returns a "false" there for removing the number that I need to stay.
I included my sheet so you can look at it. I made my changes to Week 19, DE4 and used DD2 for date info.
What I am looking for is for DG4 to calculate on a certain day. Your addition calculates 2 days after date in DD2. On third Day DG4 reverts to a "FALSE" and clears my number. I need it to calculate on a certain day and retain that information for 18 weeks in that cell DG4. Because the number that it is looking up can change from week to week.
So what I do now is calculate all players in column DG and then copy and paste value each week. But I can not figure out how to keep the numbers in DG from changing once it has calculated on its correct date.
I hope this makes sense.
Thank You
James Pape
Hi Sam,
Yes, IF works. Concrete formula depends on your data structure, like
=IF(TODAY()>DATEVALUE("2018-06-01"),<ref on the cell with May sum>,"")and date in your locale format
SergeiBaklan is it possible for me to create a cell of text that appears after I enter a date? For example if i entered between the dates of "1965-1975" how do I make it say 'pinapple' or anything like that. I hope you understand what I'm asking? (If it is possible? How?)
Afraid not, it's better with sample file.
If you enter some text which includes dates in which exactly format you enter them (with dash, dash and spaces around, whatever).
If you enter couple of years how Excel knows this exactly range shall be marked as 'pinapple', not something else?
If ranges are overlapping?
Where to show marks?
Perhaps something else, but better illustrate on the file with manually added desired result.
Thank you. I think I'm almost there, but it's still giving me an error. I think I'm still making a small error somewhere in the formula you gave me. Let me be more specific, and if you don't mind, you can tell me how to write the formula.
If the date is equal to or beyond July 1st 2018, I want the cell to populate the value from cell =Jun!V24. What would that look like? I tried copying your formula so it said =IF(today()>DATEVALUE("2018-07-01"), <=Jun!V24>, " "). What do I need to fix?
Thanks so muchSam, the formula will be like
=IF(TODAY()>=DATEVALUE("2018-07-01"), Jun!V24>, "")the only you have to use the date in your format. For US locale that will be like
=IF(TODAY()>=DATEVALUE("07/01/2018"), Jun!V24>, "")
