Google Sheets Query - Date Driven Formula

N0t Y0urs

Board Regular
Joined
May 1, 2022
Messages
96
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Platform
  1. MacOS
  2. Mobile
  3. Web
I have included the example in excel so I could use XL2BB.

If you look at the mini table I have posted it shows 5 different scenarios. Ultimately what I want is to Update "start date" C2, then if i want it to occur two times a day I would change C3 to 2 and it would adjust the dates to reflect the start date with another occurrence. As you can see I am only wanting weekdays and every 6th line has data but the date is not factored into the equation therefore it is skipped. If I need to report the date to simplify the end result it would be the same as the previous line. Example B12 would equal B11, G12 would equal F12 and so on.

Is there a way of achieving this in ideally Google Sheets or worst case Excel. The reason for not using excel is that these workings are predominately used on tablets or mobile devices which force you down the Google Path.

Thanks in advance and I hope the example helps explain what I am trying to achieve

So what I am wanting to achieve is this.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2Date3/11/24Date3/11/24Date3/11/24Date3/11/24Date3/11/24
3Number1Number2Number3Number4Number5
4
5
6DateLevelBlah BlahBlah BlahDateLevelBlah BlahBlah BlahDateLevelBlah BlahBlah BlahDateLevelBlah BlahBlah BlahDateLevelBlah BlahBlah Blah
7Mon 11-Mar-241Blah BlahBlah BlahMon 11-Mar-241Blah BlahBlah BlahMon 11-Mar-241Blah BlahBlah BlahMon 11-Mar-241Blah BlahBlah BlahMon 11-Mar-241Blah BlahBlah Blah
8Tue 12-Mar-242Blah BlahBlah BlahMon 11-Mar-242Blah BlahBlah BlahMon 11-Mar-242Blah BlahBlah BlahMon 11-Mar-242Blah BlahBlah BlahMon 11-Mar-242Blah BlahBlah Blah
9Wed 13-Mar-243Blah BlahBlah BlahTue 12-Mar-243Blah BlahBlah BlahMon 11-Mar-243Blah BlahBlah BlahMon 11-Mar-243Blah BlahBlah BlahMon 11-Mar-243Blah BlahBlah Blah
10Thu 14-Mar-244Blah BlahBlah BlahTue 12-Mar-244Blah BlahBlah BlahTue 12-Mar-244Blah BlahBlah BlahMon 11-Mar-244Blah BlahBlah BlahMon 11-Mar-244Blah BlahBlah Blah
11Fri 15-Mar-245Blah BlahBlah BlahWed 13-Mar-245Blah BlahBlah BlahTue 12-Mar-245Blah BlahBlah BlahTue 12-Mar-245Blah BlahBlah BlahMon 11-Mar-245Blah BlahBlah Blah
12Blah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah Blah
13Mon 18-Mar-246Blah BlahBlah BlahWed 13-Mar-246Blah BlahBlah BlahTue 12-Mar-246Blah BlahBlah BlahTue 12-Mar-246Blah BlahBlah BlahTue 12-Mar-246Blah BlahBlah Blah
14Tue 19-Mar-247Blah BlahBlah BlahThu 14-Mar-247Blah BlahBlah BlahWed 13-Mar-247Blah BlahBlah BlahTue 12-Mar-247Blah BlahBlah BlahTue 12-Mar-247Blah BlahBlah Blah
15Wed 20-Mar-248Blah BlahBlah BlahThu 14-Mar-248Blah BlahBlah BlahWed 13-Mar-248Blah BlahBlah BlahTue 12-Mar-248Blah BlahBlah BlahTue 12-Mar-248Blah BlahBlah Blah
16Thu 21-Mar-249Blah BlahBlah BlahFri 15-Mar-249Blah BlahBlah BlahWed 13-Mar-249Blah BlahBlah BlahWed 13-Mar-249Blah BlahBlah BlahTue 12-Mar-249Blah BlahBlah Blah
17Fri 22-Mar-2410Blah BlahBlah BlahFri 15-Mar-2410Blah BlahBlah BlahThu 14-Mar-2410Blah BlahBlah BlahWed 13-Mar-2410Blah BlahBlah BlahTue 12-Mar-2410Blah BlahBlah Blah
18Blah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah Blah
19Mon 25-Mar-2411Blah BlahBlah BlahMon 18-Mar-2411Blah BlahBlah BlahThu 14-Mar-2411Blah BlahBlah BlahWed 13-Mar-2411Blah BlahBlah BlahWed 13-Mar-2411Blah BlahBlah Blah
20Tue 26-Mar-2412Blah BlahBlah BlahMon 18-Mar-2412Blah BlahBlah BlahThu 14-Mar-2412Blah BlahBlah BlahWed 13-Mar-2412Blah BlahBlah BlahWed 13-Mar-2412Blah BlahBlah Blah
21Wed 27-Mar-2413Blah BlahBlah BlahTue 19-Mar-2413Blah BlahBlah BlahFri 15-Mar-2413Blah BlahBlah BlahThu 14-Mar-2413Blah BlahBlah BlahWed 13-Mar-2413Blah BlahBlah Blah
22Thu 28-Mar-2414Blah BlahBlah BlahTue 19-Mar-2414Blah BlahBlah BlahFri 15-Mar-2414Blah BlahBlah BlahThu 14-Mar-2414Blah BlahBlah BlahWed 13-Mar-2414Blah BlahBlah Blah
23Fri 29-Mar-2415Blah BlahBlah BlahWed 20-Mar-2415Blah BlahBlah BlahFri 15-Mar-2415Blah BlahBlah BlahThu 14-Mar-2415Blah BlahBlah BlahWed 13-Mar-2415Blah BlahBlah Blah
24Blah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah Blah
25Mon 01-Apr-2416Blah BlahBlah BlahWed 20-Mar-2416Blah BlahBlah BlahMon 18-Mar-2416Blah BlahBlah BlahThu 14-Mar-2416Blah BlahBlah BlahThu 14-Mar-2416Blah BlahBlah Blah
26Tue 02-Apr-2417Blah BlahBlah BlahThu 21-Mar-2417Blah BlahBlah BlahMon 18-Mar-2417Blah BlahBlah BlahFri 15-Mar-2417Blah BlahBlah BlahThu 14-Mar-2417Blah BlahBlah Blah
27Wed 03-Apr-2418Blah BlahBlah BlahThu 21-Mar-2418Blah BlahBlah BlahMon 18-Mar-2418Blah BlahBlah BlahFri 15-Mar-2418Blah BlahBlah BlahThu 14-Mar-2418Blah BlahBlah Blah
28Thu 04-Apr-2419Blah BlahBlah BlahFri 22-Mar-2419Blah BlahBlah BlahTue 19-Mar-2419Blah BlahBlah BlahFri 15-Mar-2419Blah BlahBlah BlahThu 14-Mar-2419Blah BlahBlah Blah
29Fri 05-Apr-2420Blah BlahBlah BlahFri 22-Mar-2420Blah BlahBlah BlahTue 19-Mar-2420Blah BlahBlah BlahFri 15-Mar-2420Blah BlahBlah BlahThu 14-Mar-2420Blah BlahBlah Blah
30Blah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah Blah
31Mon 08-Apr-2421Blah BlahBlah BlahMon 25-Mar-2421Blah BlahBlah BlahTue 19-Mar-2421Blah BlahBlah BlahMon 18-Mar-2421Blah BlahBlah BlahFri 15-Mar-2421Blah BlahBlah Blah
32Tue 09-Apr-2422Blah BlahBlah BlahMon 25-Mar-2422Blah BlahBlah BlahWed 20-Mar-2422Blah BlahBlah BlahMon 18-Mar-2422Blah BlahBlah BlahFri 15-Mar-2422Blah BlahBlah Blah
33Wed 10-Apr-2423Blah BlahBlah BlahTue 26-Mar-2423Blah BlahBlah BlahWed 20-Mar-2423Blah BlahBlah BlahMon 18-Mar-2423Blah BlahBlah BlahFri 15-Mar-2423Blah BlahBlah Blah
34Thu 11-Apr-2424Blah BlahBlah BlahTue 26-Mar-2424Blah BlahBlah BlahWed 20-Mar-2424Blah BlahBlah BlahMon 18-Mar-2424Blah BlahBlah BlahFri 15-Mar-2424Blah BlahBlah Blah
35Fri 12-Apr-2425Blah BlahBlah BlahWed 27-Mar-2425Blah BlahBlah BlahThu 21-Mar-2425Blah BlahBlah BlahTue 19-Mar-2425Blah BlahBlah BlahFri 15-Mar-2425Blah BlahBlah Blah
36Blah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah BlahBlah Blah
37Mon 15-Apr-2426Blah BlahBlah BlahWed 27-Mar-2426Blah BlahBlah BlahThu 21-Mar-2426Blah BlahBlah BlahTue 19-Mar-2426Blah BlahBlah BlahMon 18-Mar-2426Blah BlahBlah Blah
38Tue 16-Apr-2427Blah BlahBlah BlahThu 28-Mar-2427Blah BlahBlah BlahFri 22-Mar-2427Blah BlahBlah BlahTue 19-Mar-2427Blah BlahBlah BlahMon 18-Mar-2427Blah BlahBlah Blah
39Wed 17-Apr-2428Blah BlahBlah BlahThu 28-Mar-2428Blah BlahBlah BlahFri 22-Mar-2428Blah BlahBlah BlahTue 19-Mar-2428Blah BlahBlah BlahMon 18-Mar-2428Blah BlahBlah Blah
40Thu 18-Apr-2429Blah BlahBlah BlahFri 29-Mar-2429Blah BlahBlah BlahFri 22-Mar-2429Blah BlahBlah BlahWed 20-Mar-2429Blah BlahBlah BlahMon 18-Mar-2429Blah BlahBlah Blah
41Fri 19-Apr-2430Blah BlahBlah BlahFri 29-Mar-2430Blah BlahBlah BlahMon 25-Mar-2430Blah BlahBlah BlahWed 20-Mar-2430Blah BlahBlah BlahMon 18-Mar-2430Blah BlahBlah Blah
42
Sheet1
Cell Formulas
RangeFormula
B7,V7,Q7,L7,G7B7=C2
C7,W7,R7,M7,H7C7=1
B8:C11,W38:W41,R38:R39,Q40:R40,R41,L38:M38,M39:M41,G38:H38,H39,G40:H40,H41,B38:C41,W32:W35,R32:R34,Q35:R35,L32:M32,M33:M34,L35:M35,H32,G33:H33,H34,G35:H35,B32:C35,W26:W29,Q26:R26,R27:R29,M26:M27,L28:M28,M29,G26:H26,H27,G28:H28,H29,B26:C29,W20:W23,R20B8=B7+1
G8,G10,V38:V41,Q38,Q41,L39:L40,G39,G41,V32:V35,Q32:Q34,L33,G32,G34,V26:V29,Q27:Q29,L26:L27,L29,G27,G29,V20:V23,Q20,Q22:Q23,L20,L22:L23,G20,G22,V14:V17,Q14:Q15,Q17,L15,G15,G17,V8:V11,Q8:Q10,L8:L9,L11G8=G7
B13,V37,B37,Q31,G31,B31,L25,B25,G19,B19B13=B11+3
C13,W37,R37,M37,H37,C37,V31:W31,R31,M31,H31,C31,V25:W25,R25,M25,H25,C25,V19:W19,R19,M19,H19,C19,V13:W13,R13,M13,H13C13=C11+1
G13,Q37,Q39,L37,G37,L31,L34,Q25,G25,Q19,L19,Q13,L13,L16G13=G11
L41L41=L40+3
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,223,884
Messages
6,175,173
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top