SOLVED

New Contributor

# 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.

21 Replies

# Re: Can I make a cell value appear only after a certain date?

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

# Re: Can I make a cell value appear only after a certain date?

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 much

# Re: Can I make a cell value appear only after a certain date?

Sam, 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>, "")`

# Re: Can I make a cell value appear only after a certain date?

Still not working :(  Not sure if I am doing something wrong.  Thanks for your help.

best response confirmed by Sam Maw (New Contributor)
Solution

# Re: Can I make a cell value appear only after a certain date?

Sorry, I forgot to correct the reference when copy/paste your initial formula.

` =IF(TODAY()>=DATEVALUE("07/01/2018"), Jun!V24, "")`

# Re: Can I make a cell value appear only after a certain date?

Yes!  Thank you!  I got it.  Thanks for your help!

# Re: Can I make a cell value appear only after a certain date?

@Sergei Baklan I'm trying to implement the formula you've helped @Sam Maw with... I've got it work ok, however I wanted the "TODAY" to pull from a date from another cell (C4 in my example) and I also want the "DATEVALUE" to pull a date from a selected cell (A7 in my example) see screenshot below...

I'm a bit of an Excel novice, Is this possible?

I've also attached the excel sheet

Thanks

m.

# Re: Can I make a cell value appear only after a certain date?

It's like

``=IF(\$C\$3>=DATE(LEFT(A6,4), MID(A6,5,2), RIGHT(A6,2)),\$C\$4,0)``

# Re: Can I make a cell value appear only after a certain date?

@Sergei Baklan Got it! Thanks

# Re: Can I make a cell value appear only after a certain date?

@Sergei Baklan If I didn't want the "total" (N9 in my worksheet) to exceed the inputed "total number of units" (B5 in my worksheet) how can I do that?

thanks

m.

# Re: Can I make a cell value appear only after a certain date?

That could be

``=MIN(\$B\$5,SUM(\$B\$9:\$M\$9))``

# Re: Can I make a cell value appear only after a certain date?

@Sergei Baklan , Hi I am doing a planning for some robot to run, what should I insert in the formula bar so that I will have a "x" each alternate 7 days. Excel layout is: COLUMN: DAYS (1-30)
Row: Diff Location (p.s I have a drop down list to choose from jan-dec).

# Re: Can I make a cell value appear only after a certain date?

It depends on how your data is structured. Could you please provide small sample file?

# Re: Can I make a cell value appear only after a certain date?

In Sam's example, how can you freeze that value after that date passes.. won't it change as the totals change (after the Date passes)? I'm not sure I'm wording this right

# Re: Can I make a cell value appear only after a certain date?

Sorry, not sure I understood what is required. Perhaps you may illustrate by your own sample?

# Re: Can I make a cell value appear only after a certain date?

We have a monthly membership where people join for a fee and stay any number of months, (There are 2 levels of membership) I keep track of the number of members, and the total fees incoming. I'd like to have a snapshot of those totals at the end of each month, I tried the formula you gave to Sam, but the "June" total kept updating even after June. I'd like that number to 'freeze' at the value it is on Jun 30 and not change afterwards. Then I'd have totals for each month.. ....ex. Jun had 23 members and \$941 in income, etc

# Re: Can I make a cell value appear only after a certain date?

If convert source to the table you may calculate count per month and rounding total count

by

``````=COUNTIFS(Table1[Date],">"&EOMONTH(E9,-1),
Table1[Date],"<="&EOMONTH(E9,0),
Table1[Level],\$F\$7)

and

=COUNTIFS(Table1[Date],"<="&EOMONTH(E8,0),
Table1[Level],\$F\$7)``````