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

[Sam Maw]

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. 

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[#6 · Accepted solution]

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

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

[#7]

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

[#8]

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

[#9]

@mark1900 

It's like

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

[#10]

@Sergei Baklan Got it! Thanks :folded_hands:

[#11]

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

[#12]

@mark1900 

That could be

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

[#13]

@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).
please help

[#14]

@leow98 

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

[#15]

@Sergei Baklan 

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 :) 

[#16]

@mstingle 

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

[#17]

@Sergei Baklan 

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

https://docs.google.com/spreadsheets/d/1MHbsVWtHziCHNDX2KEm8PprVq4KtZPAuVG0ZM7V1ktA/edit?usp=sharing

[#18]

@mstingle 

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)

Please check in attached.

[#19]

Ok that helps. But what if a $27 member joins in Jan and quits in Aug. Will the totals for Jan-Jul be recalculated and reduced by $27? I don't want that to change. That is what I want to be 'frozen'.
I currently just delete the member from the list... maybe that is not what I should do...

[#20]

@mstingle 

And how do we know that the member quits?  We may calculate only active members for each month if only information who are actives exists.

Or you just remove records form the table?