Date & Time Functions

This group of functions allows you to work on date and time related data.  The following are the few important functions related to this group.

TODAY()–>returns todays date based on your system’s date

NOW()–>returns both date and time

DAY()–> returns the day number

            DAY(A1)–>9 (while A1=9-6-2020)

MONTH–>returns the month number

            MONTH(A1)–>6 (while A1=9-6-2020)

YEAR–>returns the year number

            YEAR(A1)–>2020 (while A1=9-6-2020)

DATE–>returns the date based on the parameters

            DATE(a1,A2,A3)–>9/6/2020

            while (A1-2020,A2-06, A3-09)

            DATE()+5  –>date after 5 days

            DATE()-5       –>date before 5 days

if you want to add/subtract number of months use EDATE()

            EDATE(A1,4)

            where A1=date, 4=number of months to be added

Calculating Net Working Days

suppose i am working in a company where saturday and sundays are off.  how can I calculate net working days.

=NETWORKDAYS(A1,A2,[A3:A5])

where A1=start date, A2=end date, A3:A5=holidays

Calculate Networking Days More Accurately

=NETWORKDAYS.INTL(A1,A2,[11],[A3:A5])

where A1=startdate

            A2=End date

            11=weekend type

            A3:A5=holidays list

Changing Date Into Day Name Using Custom Formatting

1. select the date
2. custom formatting
3. type ‘dddd’

(OR)

=TEXT(DAY(D5),”dddd”)

Calculate Age Based On Date Of Birth

=datedif(startdate,endDate,”Y”) –>returns age in years

=datedif(startdate,endDate,”M”)–>returns age in number of months

=datedif(startdate,endDate,”D”)–>returns age in years

=datedif(startdate,endDate,”YM”)–>returns age in years

=datedif(startdate,endDate,”MD”)–>returns age in years