Date Formula in Excel

 

Date Formula

Workday Formula

=Workday(B2,C2)

	A	B	C	D
1	Task Name	Task Start Date	Duration (Working Days)	Task Finished Date
2	Task 1	1/1/2022	10	1/14/2022
3	Task 2	1/13/2022	15
4	Task 3	1/20/2022	7
5	Task 4	1/5/2022	12
6	Task 5	1/10/1900	15

 

Workday Formula (With Holiday)

=Workday(B2,C2,E2:E3)

	A	B	C	D	E	F
1	Task Name	Task Start Date	Duration (Working Days)	Task Finished Date	Holidays Date	Day
2	Task 1	1/1/2022	10	1/17/2022	1/14/2022	Fri
3	Task 2	1/13/2022	15		1/26/2022	Wed
4	Task 3	1/20/2022	7
5	Task 4	1/5/2022	12
6	Task 5	1/10/1900	15

 

Networkdays Formula    

=Networkdays(B2,C2)

	A	B	C	D
1	Task Name	Task Start Date	Task End Date	Total Working Day
2	Task 1	1/3/2022	1/20/2022	13
3	Task 2	1/13/2022	1/25/2022
4	Task 3	1/20/2022	2/15/2022
5	Task 4	1/5/2022	2/21/2022
6	Task 5	1/10/1900	2/28/2022

 

Networkdays Formula (With Holidays)

        

=Networkdays(B2,C2,E2:E3)

 

	A	B	C	D	E	F
1	Task Name	Task Start Date	Task End Date	Total Working Day	Holidays Date	Day
2	Task 1	1/3/2022	1/20/2022	14	1/14/2022	Fri
3	Task 2	1/13/2022	1/25/2022		1/26/2022	Wed
4	Task 3	1/20/2022	2/15/2022
5	Task 4	1/5/2022	2/21/2022
6	Task 5	1/10/1900	2/28/2022

        

Edate Formula

=Edate(A2,3)

	A	B
1	Policy Date	Next Renew Date After 3 Month
2	1/1/2022	4/1/2022
3	1/2/2022
4	1/3/2022
5	1/4/2022
6	1/5/2022
7	1/6/2022
8	1/7/2022
9	1/8/2022
10	1/9/2022
11	1/10/2022

 

Eomonth Formula (Next Month Last Date)

=Eomonth(A2,1)

	A	B
1	Date	Last Date of Month
2	6/12/2022	7/31/2022
3	1/2/2022
4	1/3/2022
5	1/4/2022
6	1/5/2022
7	1/6/2022
8	1/7/2022
9	1/8/2022
10	1/9/2022
11	1/10/2022

 

Eomonth Formula (Previous Month Last Date)

=Eomonth(A2,-1)

	A	B
1	Date	Last Date of Month
2	6/12/2022	5/31/2022
3	1/2/2022
4	1/3/2022
5	1/4/2022
6	1/5/2022
7	1/6/2022
8	1/7/2022
9	1/8/2022
10	1/9/2022
11	1/10/2022

 

Year Formula

=Year(B2)

	A	B	C
1	Emp ID	Joining Date	Joining Year
2	E001	12/04/2012	2012
3	E002	05/6/2014	2014
4	E003	9/07/2011	2011
5	E004	5/08/2012	2012
6	E005	08/09/2018	2018
7	E006	06/04/2012	2012
8	E007	08/03/2013	2013

 

Datedif Formula

=Datedif(A2,now(),”y”)

	A	B
1	Date of Birth	Age
2	12/10/1995	27
3	10/10/1991
4	23/5/1995
5	3/6/1993
6	2/5/2001
7	8/9/2006

Weekday Formula

=Weekday(A2)

Note- Count Week Days Start Sun to Sat.

	A	B
1	Date	Day of week
2	1/5/2003	1
3	2/9/2005	4
4	3/17/2007	7
5	4/21/2009	3
6	5/27/2011	6
7	7/1/2013	2
8	8/6/2015	5
9	9/10/2017	1
10	10/16/2019	4
11	11/20/2021	7
12	12/26/2023	3