Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #725 Sep 01, 2012 www.energyefficientbuild.com.
user DaltonJ said: (https://productforums.google.com/forum/#!mydiscussions/docs/MbgLk9V0sCw)
Issues with a formula: Date countdown function
user DaltonJ said: (https://productforums.google.com/forum/#!mydiscussions/docs/MbgLk9V0sCw)
Issues with a formula: Date countdown function
Hey everyone
I've got a little issue with something I'm trying to do for my brother. The particular issue I'm trying to fix is with a function dealing with dates. I'm trying to set up about 7 cells in a column to count down days in two week periods. For instance, each cell would start out saying 14. This would stand for 14 days. Each cell would be for a 2 week period. If I started down the countdown from today, I'd want it to countdown from 14 to 0 and that would be two weeks. The day after it hits 0, I would like the two week period in the next cell to begin counting down, and so on.
So if I have 7 cells, each starts off displaying 14. They countdown to 0, then the next cell takes over and proceeds till they've all counted down to zero. I've tried different formulas and keep finding logic errors if not just outright errors that break it. The code I've gotten to now is the following:
=if(date(2012,09,12)-TODAY()<=
I looked around and it seemed as if the best way to do it was with nested IF statements. The red section is my first statement. It says that if the target date, minus today's date, is less than or equal to "0", then display zero. This keeps it from going into the negative numbers. The green section says if it's greater than 14, then display 14. I had a problem when I went to the second cell of it being 28 and showing that. It was a logic error. In the blue section, I needed to use an OR statement. I'm not sure if that's legal. I'm not 100% sure how to use the IF statement, but this is how they showed to nest it in another post on this forum. I had to use the OR statement, because I needed to tell it that if the target date, minus today's date, is between 14 and 0, then display it. This would be my 13-1 numbers. If anyone could tell me the most consice way to write this code I would appreciate it. Also, while you're here, there are a couple of other things I'd like to do involving this.
This whole thing is because he wants to read a book series, but has trouble without a deadline. I've set up a whole thing that tells him his average number of pages he's read, how many he has left in each book and total, etc... This issue is because I'm trying to give him 2 weeks per book. I'd like to be able to change that later, but it's what i'm going with now. I'd like to be able to display a message in an adjacent cell depending on how he's doing. Ideally, I would want a formula that would take the number of pages he's got left to read and if it falls within a certain threshold, then display a particular message. For example, if he's halfway through the book and has a week left of his two weeks, then it would say, "You're doing well"; if he has read half of the book and only has 2 days, then it would say, "Hurry up, you're falling behind". If he's finished the book, it would say, "Congradulations, you're done!". That kind of thing. At minimum, I'd want to display these messages based on what number is displayed in the 14 day cell.
Second, I'm currently changing the date in each cell that the 2 week period starts in. In the case above, it would be date(2012,09,12). For the next cell, I would change it to date(2012,09,26), etc... I'd like to have that 2 week period to extrapolate from a cell that he would enter his start date in. So if he were to start today, on 8/30/2012 (aka Cell A1), then the first cell would be date(A1 - today()), then the next countdown cell would be like, date((A1 + 2 weeks)-today()), and so on. I tried to pull the date from a cell for another simpler formula and it wouldn't do it. I think it was in the wrong format.
If you guys/gals could help me with these problems, I would really appreciate it. Thanks for reading all of this.
----------------------------------------------------------------------------------------
following presentation is a result of a little play I had with this
No comments:
Post a Comment