Populate Date to Another Workbook

MrDB4Excel · Sep 19, 2024

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

2

26

January

February

March

April

May

June

36

3

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

4

Action 1

5

Action 2

6

Action 3

6

16

26

6

16

26

6

16

26

6

16

26

6

16

26

6

16

26

7

8

26

July

August

September

October

November

December

36

30

9

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

1st

2nd

3rd

4th

5th

6th

10

11

12

6

16

26

6

16

26

6

16

26

6

16

26

6

16

26

6

16

26

13

14

15

1

Jan

Jan 1, 2024

Jan 01, 2024

31

16

2

Feb

Feb 1, 2024

Feb 01, 2024

29

17

3

Mar

Mar 1, 2024

Mar 01, 2024

31

18

4

Apr

Apr 1, 2024

Apr 01, 2024

30

19

5

May

May 1, 2024

May 01, 2024

31

20

6

Jun

Jun 1, 2024

Jun 01, 2024

30

21

7

Jul

Jul 1, 2024

Jul 01, 2024

31

22

8

Aug

Aug 1, 2024

Aug 01, 2024

31

23

9

Sep

Sep 1, 2024

Sep 01, 2024

30

24

10

Oct

Oct 1, 2024

Oct 01, 2024

31

25

11

Nov

Nov 1, 2024

Nov 01, 2024

30

26

12

Dec

27

28

29

06

30

16

31

9/6/2024

26

September 26

32

33

34

September 6

35

September 16

36

September 26

37

38

39

Sheet1

Cell Formulas
Range	Formula
A2,A8	A2	=OFFSET(B6,0,MATCH(MAX(B6:AK6)+1,B6:AK6,1)-1)
AM2,AM8	AM2	=A2+10
AP8:AP9	AP8	=XLOOKUP($AM$30,$AN$15:$AN$26,$AR$15:$AR$26,0)
A10:A12	A10	=A4
AP15:AP26	AP15	=AN15&" 1, "&$AN$27
AQ15:AQ26	AQ15	=TEXT(AP15,"mmm dd, yyyy")
AR15:AR26	AR15	=DAY(EOMONTH(AQ15,0))
AN27	AN27	=YEAR(AM27)
AM27	AM27	=TODAY()
AM28	AM28	=TEXT($AM$27,"mmmm")
AM29	AM29	=AM28&" "&AM8
AM30	AM30	=TEXT($AM$27,"mmm")
AP29:AP31	AP29	=TEXT(AM34,"mmmm dd")
AM34	AM34	=$AM$28&" "&$AN$29
AM35	AM35	=$AM$28&" "&$AN$30
AM36	AM36	=$AM$28&" "&$AN$31

myall_blues · Sep 20, 2024

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"))

Book1

A

B

C

1

Today:

2

20/09/2024

Next due date is September 26th

3

Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()
B2	B2	=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"))

MrDB4Excel · Sep 20, 2024

myall_blues said:
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"))

Book1
A B C
1 Today:
2 20/09/2024 Next due date is September 26th
3
Sheet1
Cell Formulas
Range Formula
A2 A2 =TODAY()
B2 B2 =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.

myall_blues · Sep 20, 2024

MrDB4Excel said:
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?

MrDB4Excel · Sep 20, 2024

myall_blues said:
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?

MrDB4Excel · Sep 20, 2024

Now it works, text in that cell must have been white. Thanks loads, this is a very big help.

myall_blues · Sep 20, 2024

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")))

MrDB4Excel · Sep 20, 2024

myall_blues said:
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!!!

Populate Date to Another Workbook

MrDB4Excel

Active Member

Attachments

Excel Facts

myall_blues

Well-known Member

MrDB4Excel

Active Member

myall_blues

Well-known Member

MrDB4Excel

Active Member

MrDB4Excel

Active Member

myall_blues

Well-known Member

MrDB4Excel

Active Member

Similar threads

Forum statistics

Share this page

Populate Date to Another Workbook

Active Member

Attachments

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Active Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock