How do I extract business day from a date?

[mcmuney]

Example:

5/1/18 Sat
5/2/18 Sun
5/3/18 Mon (what formula can extract from 5/3/18 that it's business day 1?; just need "1")

Using the same series of dates from above, 5/10/18 would be "6".

Thanks in advance.

 

[DRSteele]

Those dates don't exist. May 3, 2018 is a Thursday.

 

[mcmuney]

Those dates don't exist. May 3, 2018 is a Thursday.

I was using them as an example. You can use 9/3/18, which falls on Monday.

 

[jtakw]

Hi,

Book1
	A	B	C
1	5/1/2018		2
2	5/2/2018		3
3	5/3/2018		4
4	5/10/2018		4
5	9/3/2018		1
Sheet46

Cell Formulas
Range	Formula
C1	=WEEKDAY(A1,2)

Formula copied down.

 

[mcmuney]

Hi,

	A	B	C

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5/2/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5/3/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5/10/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]9/3/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet46​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=WEEKDAY(A1,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Formula copied down.

Column C cannot repeat until the next month. Here's how it should be for May:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]#[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Business Day[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]5/1/2018[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]5/2/2018[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]5/3/2018[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]5/4/2018[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5/7/2018[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Your sample and description wasn't clear and didn't match up.

This will give you business day count (Monday thru Friday), you'll need an Empty or Header row between months, and it does Not account for holidays:

Book1
	A	B	F
1	Date		Business Day
2	5/1/2018		1
3	5/2/2018		2
4	5/3/2018		3
5	5/4/2018		4
6	5/5/2018
7	5/6/2018
8	5/7/2018		5
9	5/8/2018		6
10	5/9/2018		7
11	5/10/2018		8
12	5/11/2018		9
13	5/12/2018
Sheet46

Cell Formulas
Range	Formula
F2	=IF(OR(WEEKDAY(A2,2)={6,7}),"",IFERROR(LOOKUP(9.99999999999999E+307,F$1:F1),0)+1)

Formula copied down.