Why does this formula not work today?

L_123L_123 ✭✭✭✭✭
edited 06/01/20 in Formulas and Functions
06/01/20 Edited 06/01/20
Answered - Pending Review

=DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - WEEKDAY(TODAY()))


It is the Weekday part of the formula that is stopping it from running. Using INT and VALUE and whatever else I can think of doesn't work.


@Genevieve P

Answers

  • L_123L_123 ✭✭✭✭✭

    Never mind. GDI

  • Hi @[email protected]

    Did you figure it out? Was it the parenthesis?

    =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())) - WEEKDAY(TODAY())

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I think it was this portion...

    DAY(TODAY()) - WEEKDAY(TODAY())


    3 - 4 = -1

    -1 can't be used as a day within a DATE function.


    @[email protected] Is that what it was?

    thinkspi.com

  • L_123L_123 ✭✭✭✭✭

    @Paul Newcome @Genevieve P

    Yeah paul got it... this was part of a long running very complex sheet that gathers and outputs current month KPI's. I guess people just weren't checking it in the beginning of the month and/or guessing there just wasn't data in the charts yet. About panicked when I saw it had failed. Ended up simplifying the formula to the below:

    =TODAY() - WEEKDAY(TODAY()) + 1

    Actually the entire formula is:

    =TODAY() - WEEKDAY(TODAY()) + 1 + "-" + (TODAY() - WEEKDAY(TODAY()) - 1 + 8)

    to output the current week, I just simplified it for this question.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @[email protected] Wouldn't "+ 7" work the same as "-1 + 8"? I just know you like to simplify when you can, so...

    thinkspi.com

  • L_123L_123 ✭✭✭✭✭

    Lol yeah, I spotted that after I posted it and questioned if I should fix it or not. The reason it is like that is to have a pattern between all the values I've posted, it makes it easier to troubleshoot. But you are correct it could be made more efficient with

    =TODAY() - WEEKDAY(TODAY()) + 1 + "-" + (TODAY() - WEEKDAY(TODAY()) +7

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Haha. I noticed you had used +7 in your other thread where you are tracking your more commonly used formulas (which is a great idea).


    Having said that... Sometimes it is actually better to go with -1+8 though if it maintains a pattern for troubleshooting. I have a few formulas of my own that are more "complicated" than they really need to be simply because I wanted to keep a specific pattern throughout the sheet for easier troubleshooting.

    thinkspi.com

Sign In or Register to comment.