Return value to a weekday closest to certain date

[Ashleytaylor1702]

Hi,

I’m trying to return salary (from one cell) in to a cell next to the 28th (calendar as list) or Friday prior to the 28th if the 28th falls on weekend or bank holiday. Any help greatly appreciated.

Thanks

[AliGW]

Welcome to the forum!

We can't guess your layout, so please attach a sample workbook.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[NickNunes]

You could use the formula weekday to get the day of the week it corresponds to. Combine it with an IF statement to make sure you get a Friday, but, as Ali mentioned, without a file or something to go by it will be pretty hard to know exactly what you need.

[Ashleytaylor1702]

Sorry about my first post please see the attached file.

Basically I would like a formula that I could put in D1 then auto fill down to the bottom of the date range.

I need the formula to pick up cell a6 IF it’s the 28th AND also if the 28th falls on a weekend then move the cell to the Friday prior to the weekend?

Many thanks for any help

[AliGW]

See if you can adapt the formulae here to do what you want:

Excel 2016 (Windows) 32 bit

	C	D	E	F	G
1	10 July 2018		4	0	10
2	11 July 2018		5	0	11
3	12 July 2018		6	0	12
4	13 July 2018		7	0	13
5	14 July 2018		1	0	14
6	15 July 2018		2	0	15
7	16 July 2018		3	0	16
8	17 July 2018		4	0	17
9	18 July 2018		5	0	18
10	19 July 2018		6	0	19
11	20 July 2018		7	0	20
12	21 July 2018		1	0	21
13	22 July 2018		2	0	22
14	23 July 2018		3	0	23
15	24 July 2018		4	0	24
16	25 July 2018		5	0	25
17	26 July 2018		6	0	26
18	27 July 2018	Salary here	7	1	27
19	28 July 2018		1	0	28
20	29 July 2018		2	0	29
21	30 July 2018		3	0	30
22	31 July 2018		4	0	31
23	01 August 2018		5	0	1
24	02 August 2018		6	0	2
25	03 August 2018		7	0	3
26	04 August 2018		1	0	4
27	05 August 2018		2	0	5
28	06 August 2018		3	0	6
29	07 August 2018		4	0	7
30	08 August 2018		5	0	8
31	09 August 2018		6	0	9

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	E	F	G
1	=WEEKDAY(C1,16)	=IF(AND(G1=28,E1>2),1,IF(AND(E1=7,OR(G1=26,G1=27)),1,0))	=DAY(C1)

Sheet: Sheet1

[Phuocam]

Try this in D1:

=IF(WORKDAY(C1-DAY(C1)+29,-1,holidays)=C1,"Salary here","")

[Ashleytaylor1702]

Hi,


This has been a big help,

Would it be possible to change column F from showing 1 when its on the 28th and instead show cell A6 (in the test sheet I posted earlier)?

Many Thanks

[AliGW]

Yes, like this:

=IF(AND(G1=28,E1>2),$A$6,IF(AND(E1=7,OR(G1=26,G1=27)),$A$6,0))

So, your composite formula would be:

=IF(AND(DAY(C1)=28,WEEKDAY(C1,16)>2),$A$6,IF(AND(WEEKDAY(C1,16)=7,OR(DAY(C1)=26,DAY(C1)=27)),$A$6,0))

I am presuming you were replying to me.

[Ashleytaylor1702]

Thanks a lot for your help Ali.

All working as it should be!

[AliGW]

Glad to have helped!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.