Jun 06 2018
08:56 AM
- last edited on
Jul 31 2018
08:22 AM
by
TechCommunityAP
Jun 06 2018
08:56 AM
- last edited on
Jul 31 2018
08:22 AM
by
TechCommunityAP
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.
Jun 06 2018 10:20 AM
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
Jun 06 2018 10:32 AM
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
Jun 06 2018 10:40 AM
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>, "")
Jun 06 2018 10:48 AM
Still not working :( Not sure if I am doing something wrong. Thanks for your help.
Jun 06 2018 11:01 AM
SolutionSorry, I forgot to correct the reference when copy/paste your initial formula.
=IF(TODAY()>=DATEVALUE("07/01/2018"), Jun!V24, "")
Jun 06 2018 12:24 PM
Yes! Thank you! I got it. Thanks for your help!
Jan 23 2021 08:18 AM
@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.
Jan 23 2021 08:35 AM
Jan 23 2021 08:42 AM
@Sergei Baklan Got it! Thanks :folded_hands:
Jan 23 2021 09:11 AM
@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.
Jan 24 2021 09:03 AM
Apr 11 2021 07:31 PM
Apr 12 2021 08:48 AM
It depends on how your data is structured. Could you please provide small sample file?
Apr 14 2021 06:20 PM
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 :)
Apr 15 2021 03:38 AM
Sorry, not sure I understood what is required. Perhaps you may illustrate by your own sample?
Apr 15 2021 05:48 AM - edited Apr 15 2021 05:52 AM
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
Apr 15 2021 06:56 AM
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.
Apr 15 2021 07:51 AM
Apr 15 2021 08:01 AM
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?
Jun 06 2018 11:01 AM
SolutionSorry, I forgot to correct the reference when copy/paste your initial formula.
=IF(TODAY()>=DATEVALUE("07/01/2018"), Jun!V24, "")