Populate Date to Another Workbook

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
348
Office Version
  1. 2013
Platform
  1. Windows
In this workbook I have a column showing Actions 1, 2, or 3. In Action 3 I have setup to schedule this 3 times each month. On the 6th, the 16th, and the 26th.
As you can see these dates fall within specific weeks of a month and specific days of those weeks depending on whether such dates occur within the 1st, 2nd, 3rd, 4th, 5th, or 6th week of the month.
What I would like to happen is when any one of these 36 dates match the date of “TODAY”, as in =TODAY() and per the current month that the formula result is linked to another workbook (for the purpose of this discussion I will name this other workbook WebQuery2024) where it states the relevant action is due for being done for example on September 26 even though today’s date is September 19, or September 17 or 23 or whatever today’s date happens to be.
As you can see I have tried several different approaches but I just can’t seem to get it to work.
All previous attempts were formulas I found but have since disappeared from the workbook as they didn’t work so I moved onto other formulas.
Another example may be, let’s say today’s date is October 5, then on WebQuery2024 it would show the next due date is October 6. Similarly if today’s date is October 8 then the due date would show on WebQuery2024 as October 16.
I hope this is clear enough.

ACS-Testing.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
226JanuaryFebruaryMarchAprilMayJune36
31st2nd3rd4th5th6th1st2nd3rd4th5th6th1st2nd3rd4th5th6th1st2nd3rd4th5th6th1st2nd3rd4th5th6th1st2nd3rd4th5th6th
4Action 1
5Action 2
6Action 3616266162661626616266162661626
7
826JulyAugustSeptemberOctoberNovemberDecember3630
91st2nd3rd4th5th6th1st2nd3rd4th5th6th1st2nd3rd4th5th6th1st2nd3rd4th5th6th1st2nd3rd4th5th6th1st2nd3rd4th5th6th30
10Action 1
11Action 2
12Action 3616266162661626616266162661626
13
14
151JanJan 1, 2024Jan 01, 202431
162FebFeb 1, 2024Feb 01, 202429
173MarMar 1, 2024Mar 01, 202431
184AprApr 1, 2024Apr 01, 202430
195MayMay 1, 2024May 01, 202431
206JunJun 1, 2024Jun 01, 202430
217JulJul 1, 2024Jul 01, 202431
228AugAug 1, 2024Aug 01, 202431
239SepSep 1, 2024Sep 01, 202430
2410OctOct 1, 2024Oct 01, 202431
2511NovNov 1, 2024Nov 01, 202430
2612DecDec 1, 2024Dec 01, 202431
279/19/20242024
28September
29September 3606September 06September 06
30Sep16September 16
319/6/202426September 26
32
33
34September 6
35September 16
36September 26
37
38
39
Sheet1
Cell Formulas
RangeFormula
A2,A8A2=OFFSET(B6,0,MATCH(MAX(B6:AK6)+1,B6:AK6,1)-1)
AM2,AM8AM2=A2+10
AP8:AP9AP8=XLOOKUP($AM$30,$AN$15:$AN$26,$AR$15:$AR$26,0)
A10:A12A10=A4
AP15:AP26AP15=AN15&" 1, "&$AN$27
AQ15:AQ26AQ15=TEXT(AP15,"mmm dd, yyyy")
AR15:AR26AR15=DAY(EOMONTH(AQ15,0))
AN27AN27=YEAR(AM27)
AM27AM27=TODAY()
AM28AM28=TEXT($AM$27,"mmmm")
AM29AM29=AM28&" "&AM8
AM30AM30=TEXT($AM$27,"mmm")
AP29:AP31AP29=TEXT(AM34,"mmmm dd")
AM34AM34=$AM$28&" "&$AN$29
AM35AM35=$AM$28&" "&$AN$30
AM36AM36=$AM$28&" "&$AN$31
 

Attachments

  • 2024 Calendar.png
    2024 Calendar.png
    78.6 KB · Views: 8

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Would it work for you if you just ignored all the 'Action 3' stuff and instead used this formula in 'WebQuery2024'?
Excel Formula:
=IF(AND(DAY(TODAY())>=6,DAY(TODAY())<16),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 16 & "th",IF(AND(DAY(TODAY())>=16,DAY(TODAY())<26),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 26 & "th","Next due date is " & TEXT((MONTH(TODAY())+1)*29,"mmmm ") & 6 & "th"))

Cell Formulas
RangeFormula
A2A2=TODAY()
B2B2=IF(AND(DAY(TODAY())>=6,DAY(TODAY())<16),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 16 & "th",IF(AND(DAY(TODAY())>=16,DAY(TODAY())<26),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 26 & "th","Next due date is " & TEXT((MONTH(TODAY())+1)*29,"mmmm ") & 6 & "th"))
 
Upvote 0
Would it work for you if you just ignored all the 'Action 3' stuff and instead used this formula in 'WebQuery2024'?
Excel Formula:
=IF(AND(DAY(TODAY())>=6,DAY(TODAY())<16),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 16 & "th",IF(AND(DAY(TODAY())>=16,DAY(TODAY())<26),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 26 & "th","Next due date is " & TEXT((MONTH(TODAY())+1)*29,"mmmm ") & 6 & "th"))

Cell Formulas
RangeFormula
A2A2=TODAY()
B2B2=IF(AND(DAY(TODAY())>=6,DAY(TODAY())<16),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 16 & "th",IF(AND(DAY(TODAY())>=16,DAY(TODAY())<26),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 26 & "th","Next due date is " & TEXT((MONTH(TODAY())+1)*29,"mmmm ") & 6 & "th"))
I added this formula to the WebQuery file and at today's date it did nothing but I suspect by the 26th something will show. I will let you know how it goes. BTW in A2 is the =TODAY() formula.
I am also puzzled after reading through your formula I would think something should show in the result.
 
Upvote 0
I am also puzzled after reading through your formula I would think something should show in the result.
Me too. It should show something as the TODAY() is built into the formula. Is it possible the cell is formatted to have white text or something? If you paste the formula into a new, blank workbook do you get a result?
 
Upvote 0
Me too. It should show something as the TODAY() is built into the formula. Is it possible the cell is formatted to have white text or something? If you paste the formula into a new, blank workbook do you get a result?
 
Upvote 0
Now it works, text in that cell must have been white. Thanks loads, this is a very big help.
 
Upvote 0
You're welcome. I'm glad it worked for you. Looking at it again made me realise there is a minor error in the original formula as it moves into the first five days of the new month - use this instead:
Excel Formula:
=IF(AND(DAY(TODAY())>=6,DAY(TODAY())<16),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 16 & "th",
IF(AND(DAY(TODAY())>=16,DAY(TODAY())<26),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 26 & "th",
IF(AND(DAY(TODAY())>=1,DAY(TODAY())<6),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 6 & "th","Next due date is " & TEXT((MONTH(TODAY())+1)*29,"mmmm ") & 6 & "th")))
 
Upvote 0
You're welcome. I'm glad it worked for you. Looking at it again made me realise there is a minor error in the original formula as it moves into the first five days of the new month - use this instead:
Excel Formula:
=IF(AND(DAY(TODAY())>=6,DAY(TODAY())<16),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 16 & "th",
IF(AND(DAY(TODAY())>=16,DAY(TODAY())<26),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 26 & "th",
IF(AND(DAY(TODAY())>=1,DAY(TODAY())<6),"Next due date is " & TEXT(MONTH(TODAY())*29,"mmmm ") & 6 & "th","Next due date is " & TEXT((MONTH(TODAY())+1)*29,"mmmm ") & 6 & "th")))
Thanks for finding the error, I probably would not have discovered, but perhaps, who knows. I had used Excel professionally for 20 plus years and now while in retirement for 9 plus years so it is possible I may have discovered.
Thanks again myall_blues, much appreciated!!!
 
Upvote 0

Forum statistics

Threads
1,224,550
Messages
6,179,462
Members
452,915
Latest member
hannnahheileen

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