1

I have seven spreadsheets, one for each day of the week. I am trying to get each one to display their date for that week. So for example, this week Monday would display 7/16/2018 and the Tuesday spreadsheet would display 7/17/2018. Even if it is Monday, I wanna be able to open the Tuesday spreadsheet and have it display the date of what that upcoming Tuesday will be. I've been messing around with different formulas and can't figure it out.

0

2 Answers 2

2

Monday Sheet:

=WORKDAY.INTL(TODAY()-1,1,"0111111")

String method: weekends can be specified using seven 0’s and 1’s, where the first number in the set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the day is a weekend. For example, “0000011” would mean Saturday and Sunday are weekends.

1
  • sheet Monday:
    • A1: =DATE(2018;7;16) // =DATE(2018,7,16)
  • sheet Tuesday:
    • A1: ='sheet Monday'!A1+1
  • sheet Wensday:
    • A1: ='sheet Monday'!A1+2
  • sheet Thursday:
    • A1: ='sheet Monday'!A1+3
  • etc.

if you need something more complex (like auto-updating), you can use old pure brute force:

european syntax - sheet Monday > A1:

=IF(AND(DAYS360("1.1.2018";TODAY())>=195;DAYS360("1.1.2018";TODAY())<202);"16.07.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=202;DAYS360("1.1.2018";TODAY())<209);"23.07.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=209;DAYS360("1.1.2018";TODAY())<216);"30.07.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=216;DAYS360("1.1.2018";TODAY())<223);"06.08.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=223;DAYS360("1.1.2018";TODAY())<230);"13.08.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=230;DAYS360("1.1.2018";TODAY())<237);"20.08.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=237;DAYS360("1.1.2018";TODAY())<244);"27.08.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=244;DAYS360("1.1.2018";TODAY())<251);"03.09.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=251;DAYS360("1.1.2018";TODAY())<258);"10.09.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=258;DAYS360("1.1.2018";TODAY())<265);"17.09.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=265;DAYS360("1.1.2018";TODAY())<272);"24.09.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=272;DAYS360("1.1.2018";TODAY())<279);"01.10.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=279;DAYS360("1.1.2018";TODAY())<286);"08.10.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=286;DAYS360("1.1.2018";TODAY())<293);"15.10.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=293;DAYS360("1.1.2018";TODAY())<300);"22.10.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=300;DAYS360("1.1.2018";TODAY())<307);"29.10.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=307;DAYS360("1.1.2018";TODAY())<314);"05.11.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=314;DAYS360("1.1.2018";TODAY())<321);"12.11.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=321;DAYS360("1.1.2018";TODAY())<328);"19.11.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=328;DAYS360("1.1.2018";TODAY())<335);"26.11.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=335;DAYS360("1.1.2018";TODAY())<342);"03.12.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=342;DAYS360("1.1.2018";TODAY())<349);"10.12.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=349;DAYS360("1.1.2018";TODAY())<356);"17.12.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=356;DAYS360("1.1.2018";TODAY())<363);"24.12.2018";
 IF(AND(DAYS360("1.1.2018";TODAY())>=363;DAYS360("1.1.2018";TODAY())<370);"31.12.2018"
 )))))))))))))))))))))))))

american syntax - sheet Monday > A1:

=IF(AND(DAYS360("1/1/2018",TODAY())>=195,DAYS360("1/1/2018",TODAY())<202),"7/16/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=202,DAYS360("1/1/2018",TODAY())<209),"7/23/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=209,DAYS360("1/1/2018",TODAY())<216),"7/30/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=216,DAYS360("1/1/2018",TODAY())<223),"8/6/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=223,DAYS360("1/1/2018",TODAY())<230),"8/13/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=230,DAYS360("1/1/2018",TODAY())<237),"8/20/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=237,DAYS360("1/1/2018",TODAY())<244),"8/27/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=244,DAYS360("1/1/2018",TODAY())<251),"9/3/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=251,DAYS360("1/1/2018",TODAY())<258),"9/10/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=258,DAYS360("1/1/2018",TODAY())<265),"9/17/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=265,DAYS360("1/1/2018",TODAY())<272),"9/24/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=272,DAYS360("1/1/2018",TODAY())<279),"10/1/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=279,DAYS360("1/1/2018",TODAY())<286),"10/8/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=286,DAYS360("1/1/2018",TODAY())<293),"10/15/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=293,DAYS360("1/1/2018",TODAY())<300),"10/22/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=300,DAYS360("1/1/2018",TODAY())<307),"10/29/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=307,DAYS360("1/1/2018",TODAY())<314),"11/5/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=314,DAYS360("1/1/2018",TODAY())<321),"11/12/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=321,DAYS360("1/1/2018",TODAY())<328),"11/19/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=328,DAYS360("1/1/2018",TODAY())<335),"11/26/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=335,DAYS360("1/1/2018",TODAY())<342),"12/3/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=342,DAYS360("1/1/2018",TODAY())<349),"12/10/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=349,DAYS360("1/1/2018",TODAY())<356),"12/17/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=356,DAYS360("1/1/2018",TODAY())<363),"12/24/2018",
 IF(AND(DAYS360("1/1/2018",TODAY())>=363,DAYS360("1/1/2018",TODAY())<370),"12/31/2018"
 )))))))))))))))))))))))))
4
  • "1.1.2018" is not a accepted date format in US. To avoid confusion, We may use DATE(2018;1;1) or plain "yyyy-mm-dd"
    – TheMaster
    Jul 18, 2018 at 20:31
  • @I'-'I I see, I got it from help page so I assumed there is only one correct syntax. thx for pointing it out.
    – user0
    Jul 18, 2018 at 20:34
  • Really sorry for the confusion. I meant as the formula =DATE(year,month,day) instead of quoted dates.So,that will be independent of locale. There's no "2018;1;1" here either. There's "2018/1/1".
    – TheMaster
    Jul 18, 2018 at 23:38
  • 1
    @I'-'I DAYS360("7/16/1969", "7/24/1969", 1) says US sheet
    – user0
    Jul 19, 2018 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.