Better living through Excel calculations



  • I wish I could post a screenshot of this to back up my story.

    Every year someone in my company makes up a calendar for that year to show business holidays, special corporate events, etc etc. They create this calendar in Excel, for reasons I still have yet to understand. The days of the month are laid out as if you were looking at a normal calendar, 7 days in a 1-week row; all the months are organized into three columns, four rows. I bring this up because I'm not sure of any Excel auto-formatting options that can produce this layout.

    It's not so bad to spend a couple hours every year making this cell-shaded, neatly-formatted calendar. The problem comes in when I noticed the formulas. For the first day of each month, it's just a "1" in that cell. The next day is "= A1 + 1". The next day is "= B1 + 1". Yep, they're just incrementing the value of the previous day's cell. Instead of typing 1, 2, 3 for the dates, they're entering the first day of each month, and 353 formulas to increment those values.

    And before you argue that Excel has some copy-n-paste features that will update the formulas for you, the format of the formula changes intermittently throughout the document from "= Cell + 1" to "=+Cell + 1" to "= 1 + Cell". Nope, this was all done by hand.



  • Well, what if the first day of September changes from 1 to 55?  Huh?!  Bet you didn't think of that, did you?



  • Geez! I thought everyone knew the proper tool to make a grid-based document is Photoshop, not Excel.



  • It's bad that they did it by hand, but I did the same myself.



    My formula look something like =Date( Year(A1), Month(A1), Day(A1)+1 )

    And I copied it everywhere, it allowed me to very quickly specify the full callendar.



  • @Manni said:

    I wish I could post a screenshot of this to back up my story.

    Every year someone in my company makes up a calendar for that year to show business holidays, special corporate events, etc etc. They create this calendar in Excel, for reasons I still have yet to understand. The days of the month are laid out as if you were looking at a normal calendar, 7 days in a 1-week row; all the months are organized into three columns, four rows. I bring this up because I'm not sure of any Excel auto-formatting options that can produce this layout.

    It's not so bad to spend a couple hours every year making this cell-shaded, neatly-formatted calendar. The problem comes in when I noticed the formulas. For the first day of each month, it's just a "1" in that cell. The next day is "= A1 + 1". The next day is "= B1 + 1". Yep, they're just incrementing the value of the previous day's cell. Instead of typing 1, 2, 3 for the dates, they're entering the first day of each month, and 353 formulas to increment those values.

    And before you argue that Excel has some copy-n-paste features that will update the formulas for you, the format of the formula changes intermittently throughout the document from "= Cell + 1" to "=+Cell + 1" to "= 1 + Cell". Nope, this was all done by hand.

    That's actually a clever idea, not a WTF.  Suppose you have your Excel calendar template created, and you do exactly what you said they did -- each formula is the value of the previous day's cell +1.  Now, for Januray, the first day of the month, is say, Monday.  You put 1 in Monday on the first row, and bam! -- the calendar is updated.  you just delete the contents of the cells for days >31 and before the 1st, SAVE AS "january.xls" and off you go.  Next month, you go back to the template, maybe the first day is Thursday, pop a 1 in that cell, and your calendar is updated instantly again. 

    Otherwise, every time you create a new month. you go into *every* cell and type in 2,3,4,5,..31 (or drag and use autofill, but you get the idea).  The formula saves you from this work. 

    So, I don't see why anyone would complain about this or spend too much time worrying about it, to be honest.  The result comes out fine, and for the person maintaining the calendar, it saves a little bit of work, and at the very least it prevents potential "typos" that might occur if you have to type in 1...31 each month over and over.



  • When all you have is Excel, every problem looks like a nail worksheet.

    If I had to use a spreadsheet for this, I would import a file generated
    by "cal -y". It may well be easier to use a PIM for this, though.



  • @Jeff S said:

    @Manni said:

    I wish I could post a screenshot of this to back up my story.

    Every year someone in my company makes up a calendar for that year to show business holidays, special corporate events, etc etc. They create this calendar in Excel, for reasons I still have yet to understand. The days of the month are laid out as if you were looking at a normal calendar, 7 days in a 1-week row; all the months are organized into three columns, four rows. I bring this up because I'm not sure of any Excel auto-formatting options that can produce this layout.

    It's not so bad to spend a couple hours every year making this cell-shaded, neatly-formatted calendar. The problem comes in when I noticed the formulas. For the first day of each month, it's just a "1" in that cell. The next day is "= A1 + 1". The next day is "= B1 + 1". Yep, they're just incrementing the value of the previous day's cell. Instead of typing 1, 2, 3 for the dates, they're entering the first day of each month, and 353 formulas to increment those values.

    And before you argue that Excel has some copy-n-paste features that will update the formulas for you, the format of the formula changes intermittently throughout the document from "= Cell + 1" to "=+Cell + 1" to "= 1 + Cell". Nope, this was all done by hand.

    That's actually a clever idea, not a WTF.  Suppose you have your Excel calendar template created, and you do exactly what you said they did -- each formula is the value of the previous day's cell +1.  Now, for Januray, the first day of the month, is say, Monday.  You put 1 in Monday on the first row, and bam! -- the calendar is updated.  you just delete the contents of the cells for days >31 and before the 1st, SAVE AS "january.xls" and off you go.  Next month, you go back to the template, maybe the first day is Thursday, pop a 1 in that cell, and your calendar is updated instantly again. 

    Otherwise, every time you create a new month. you go into *every* cell and type in 2,3,4,5,..31 (or drag and use autofill, but you get the idea).  The formula saves you from this work. 

    So, I don't see why anyone would complain about this or spend too much time worrying about it, to be honest.  The result comes out fine, and for the person maintaining the calendar, it saves a little bit of work, and at the very least it prevents potential "typos" that might occur if you have to type in 1...31 each month over and over.

    Jeff, I don't think you understand what was stated. While yes, if this calendar was done on a month to month basis... doind something like this might save some time. However, this calendar is made for all 12 months listed in 3 columns and 4 rows. It just doesn't quite work that nicely this way. The way it is/was implemented is indeed a WTF.



  • "doing something like this might save some time"



    Indeed, I tried to make such a calendar by myself using simple formula
    replication. And I am able to make a whole calendar in 5 minutes.
    That's why I don't think about this idea as a WTF.



  • I understand everyone's arguments about how it's quicker this way to create a calendar. It's true that just modifying a couple of values will set it up perfectly for next year, except for the fact that almost all events have to be changed. As it is, they're hardcoded to be the 2nd Tuesday in January, or every other Friday throughout the year. That 2nd Tuesday in January needs to be moved to match the new position of the date. You can change the dates just fine, but now all the events are sitting on the wrong days. Now you have to go back and manually fix all of them.

    I'm just saying there has to be a software package in place that makes this easier, rather than doing it all by hand in Excel.



  • @Manni said:

    I understand everyone's arguments about how it's quicker this way to create a calendar. It's true that just modifying a couple of values will set it up perfectly for next year, except for the fact that almost all events have to be changed. As it is, they're hardcoded to be the 2nd Tuesday in January, or every other Friday throughout the year. That 2nd Tuesday in January needs to be moved to match the new position of the date. You can change the dates just fine, but now all the events are sitting on the wrong days. Now you have to go back and manually fix all of them.

    I'm just saying there has to be a software package in place that makes this easier, rather than doing it all by hand in Excel.

    Umm.. The position of the second tuesday in January, for example, should remain the same regardless of what day that happens to fall on. Events like, say, xmas, would have to be moved, because these are dependant on absolute dates.

    Unless your calendar design is really weird. :)



  • I'm sorry if I didn't explain it clearly Otto, but the problem is that most events fall on certain dates, like January 20th, or March 3rd. There are lots of these events that happen on the same day of the month every year, or other events that are always the first of the month.

    What I'm saying is that by changing all the dates with this supposedly brillant solution is rendered useless when someone has to manually go through and fix every event so it matches up with the proper day.



  • @Otto said:

    Umm.. The position of the second tuesday in
    January, for example, should remain the same regardless of what day
    that happens to fall on. Events like, say, xmas, would have to be
    moved, because these are dependant on absolute dates.

    Unless your calendar design is really weird. :)



    Not really. If the calendar is laid out in standard fashion, than the second tuesday will fall in the second row (if the 1st of the month is a sunday, monday, or tuesday), or it will fall in the third row (if the 1st of the month is wednesday through saturday)


  • @Manni said:

    I'm just saying there has to be a software package in place that makes this easier, rather than doing it all by hand in Excel.





    And there is, a brief look around found me this



  • What really rocks ... is EXCEL games :) lolololol :)

     

    Goto XL-Games:

     

    Ahhhhh.. the old snake :)

     

     



  • @XoK said:

    What really rocks ... is EXCEL games :) lolololol :)

     

    Goto XL-Games:

     

    Ahhhhh.. the old snake :)

     

     




    did anyone ever see the flight sim behind excel 2000? i had a friend show me it once.

    i might look him up and see if i can post the instructions.

    It was something as simple as 'place a certain value in a certain field, and wam - away u go.'


  • I remember it! It was Excel 97
     
    1. On a new Worksheet, Press F5
    2. Type X97:L97 and hit enter
    3. Press the tab key
    4. Hold Ctrl-Shift
    5. Click on the Chart Wizard toolbar button
    6. Use mouse to fly around - Right button forward/ Left button reverse
    N.B. You need to have direct draw installed for this egg. If you don't, you'll get the message: "This would be much more interesting if you were running with DirectDraw. But you're not, so this will have to suffice", and the credits will appear.
     
    Courtesy of www.eeggs.com 


  • @NineSisters said:

    "doing something like this might save some time"

    Indeed, I tried to make such a calendar by myself using simple formula replication. And I am able to make a whole calendar in 5 minutes. That's why I don't think about this idea as a WTF.

    but the topic starter made clear all formulas were typed by hand, no formula replication. That is what makes this a wtf



  • Brillant



  • @XoK said:

    What really rocks ... is EXCEL games :) lolololol :)

     

    Goto XL-Games:

     

    Ahhhhh.. the old snake :)



    Haha, those are great.

    I love how they did it in the frogger game.

    They set all the cells to be 3 x 3px (or so) and then just set the background color of the cells for almost all of the display.


    I wish I had that much free time.

Log in to reply