Topic Status

How Date Works No

Date Formats for Appearance No
Date Formats with TEXT No
How Time Works No
Date and Time Together No
Date and Time Functions No
Find Deadline with NETWORKDAYNo
EDATE No
EOMONTH No
Difference between Dates No
Replace Position of M and D No
Add Hours No
Highlight Weekends No
List of Dates No
Convert to Month Name to NumbNo
Date Type 31/Dec/1899
Number Type 1 Excel considers 01-Jan-1900 as 1

01/Jan/1901
367 Hence 01-Jan-1901 is 367

16/Jul/2025
45,854 45376 days starting from 01-Jan-1900

31 dec 1899 Also - any date before 1-Jan-1900 is NOT a date

 6/12/2023
Jun/2023

Right Click > Format Cells OR Ctrl + 1

Number > Category > Custom > Type
TEXT changes the value (it is NOT a date anymore)

Format only changes the appearance, not the value (it still remains a date)
a date)
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
6/12/2023
 12:00:00 AM
0.0000

11:59:59 PM
0.9931

12:00:00 PM
0.5000

1 Day 1
1 Hour 0.0416666666666667
1 Minute 0.000694444444444444
1 Second 1.15740740740741E-05
12AM starts at 0

Next Day 12AM restarts at 0 since 1 gets added to the Day

Middle of Day is 0.5

=1/24
=1/24/60
=1/24/60/60
 6/12/2023 22:38
45,089.94 45089 is the Date Part

How to separate

45,089.94
45,089.00 =TRUNC(B8) Date Part
0.94 =B8 - INT(B8) Time Part
0.94 is the Time Part
Create Date
Day 5
Month 10
Year 2024

Complete Date

Extract Parts
25/03/2024 21:40:25
Day
Month
Year
Name of Day
Day Position in Week
Week Position in Year
Hour
Month
Second
Create Time
Hour 21
Minute 40
Second 12

Complete Time #N/A

Project Start Date Time Given Deadline
1/11/2022 100
2/5/2022 120
3/17/2022 90
8/28/2022 45
9/5/2022 90
 Date Day Holiday
1-Jan-22 Saturday New Year's Day
14-Jan-22 Friday Makar Sankranti
26-Jan-22 Wednesday Republic Day
29-Mar-22 Tuesday Holi
15-Apr-22 Friday Good Friday
18-Apr-22 Monday Easter Monday
21-Apr-22 Thursday Ram Navami
29-Apr-22 Friday Buddha Purnima
1-May-22 Sunday Labor Day
10-May-22 Tuesday Eid al-Fitr
5-Jun-22 Sunday Environment Day
15-Aug-22 Monday Independence Day
19-Aug-22 Friday Muharram
30-Aug-22 Tuesday Janmashtami
2-Oct-22 Sunday Gandhi Jayanti
8-Oct-22 Saturday Dussehra
19-Oct-22 Wednesday Diwali
1-Nov-22 Tuesday Guru Nanak Jayanti
25-Dec-22 Sunday Christmas Day
1-Dec-22 Thursday New Year's Eve
Project Start Date Project End Date Time Taken
1/11/2022 4/21/2022
2/5/2022 6/5/2022
3/17/2022 6/15/2022
8/28/2022 10/12/2022
9/5/2022 12/4/2022
 Date Day Holiday
1-Jan-22 Saturday New Year's Day
14-Jan-22 Friday Makar Sankranti
26-Jan-22 Wednesday Republic Day
29-Mar-22 Tuesday Holi
15-Apr-22 Friday Good Friday
18-Apr-22 Monday Easter Monday
21-Apr-22 Thursday Ram Navami
29-Apr-22 Friday Buddha Purnima
1-May-22 Sunday Labor Day
10-May-22 Tuesday Eid al-Fitr
5-Jun-22 Sunday Environment Day
15-Aug-22 Monday Independence Day
19-Aug-22 Friday Muharram
30-Aug-22 Tuesday Janmashtami
2-Oct-22 Sunday Gandhi Jayanti
8-Oct-22 Saturday Dussehra
19-Oct-22 Wednesday Diwali
1-Nov-22 Tuesday Guru Nanak Jayanti
25-Dec-22 Sunday Christmas Day
When will the service agreement be due next?

Order Date 3-Jun-23

Contract Duration SLA Renewal

1 Year
18 months

Find manufacturing date

Expiry Date 14-Jun-24

Expiry Date of Manufacturing

1 Year
When will the service agreement be due next?

Order Date 3-Jun-23

Contract Duration SLA Renewal

30 days
End of Month
Next Month
First of Next Month
Find Difference in Years, Months, Days

Start Date End Date

2-Jun-23 1-Apr-25 1 years, 9 months, 30 days
2-Jun-23 1-Apr-25 1
2-Jun-23 1-Apr-25 21

2-Jun-23 2-Nov-23 0 years, 5 months, 0 days

2-Jun-23 19-Nov-25 2 years, 5 months, 17 days
& DATEDIF(TODAY(), TODAY()+B12, "ym")
400 1 years, 1 months &
& "DATEDIF(TODAY(),
months" TODAY()+B13, "ym")
350 0 years, 11 months &
& "DATEDIF(TODAY(),
months" TODAY()+B14, "ym")
72 0 years, 2 months & " months"
 & DATEDIF(B5, C5, "md")
& " days"
=DATEDIF(B6, C6, "y")
=DATEDIF(B7, C7, "m")

Y(), TODAY()+B12, "ym")

Y(), TODAY()+B13, "ym")
Y(), TODAY()+B14, "ym")
Replace Position

5-Feb-18
2-Aug-18
6-Sep-19
Add Hours

10:00
20:00
5:00
35:00
List of Dates

Start Date 12-Jun-23

End Date 22-Jun-23
Convert Month Name to Number to Month Name

Jan 1 =MONTH(B4 & 1) 1

Feb #VALUE! =MONTH(B5 & 1) 2
Mar #VALUE! =MONTH(B6 & 1) 3
Apr #VALUE! =MONTH(B7 & 1) 4
May #VALUE! =MONTH(B8 & 1) 5
Jun #VALUE! =MONTH(B9 & 1) 6
Jul #VALUE! =MONTH(B10 & 1) 7
Aug #VALUE! =MONTH(B11 & 1) 8
Sep #VALUE! =MONTH(B12 & 1) 9
Oct #VALUE! =MONTH(B13 & 1) 10
Nov #VALUE! =MONTH(B14 & 1) 11
Dec #VALUE! =MONTH(B15 & 1) 12
January =TEXT(DATE(1, G4, 1), "mmmm")
February =TEXT(DATE(1, G5, 1), "mmmm")
March =TEXT(DATE(1, G6, 1), "mmmm")
April =TEXT(DATE(1, G7, 1), "mmmm")
May =TEXT(DATE(1, G8, 1), "mmmm")
June =TEXT(DATE(1, G9, 1), "mmmm")
July =TEXT(DATE(1, G10, 1), "mmmm")
August =TEXT(DATE(1, G11, 1), "mmmm")
September =TEXT(DATE(1, G12, 1), "mmmm")
October =TEXT(DATE(1, G13, 1), "mmmm")
November =TEXT(DATE(1, G14, 1), "mmmm")
December =TEXT(DATE(1, G15, 1), "mmmm")