Play with Date and Time Function in Excel

PLAY WITH DATE AND TIME FUNCTIONS IN EXCEL

When we handle data have dates or date and time formatted values, we can apply DATE and TIME function in our reference. We will see the process steps with few example that I have plotted for you in gif format sheet.

In above example I have a date in cell "B4" which is Date and time formatted. Most of extracted data for SQL, Web data, Access tables or other application we see the date is captured in Date and time format. However using these dates in references or formulas in excel we may get incorrect result, hence to avoid such circumstances, we come across of date and time functions.

To get day name from the Date the formula applied 
             = TEXT(B4,"DDD") for short name 
Result:  Sat
             =TEXT(B4,"DDDD") for long name format
Result:  Saturday

To extract the month name again 
            =TEXT(B4,"MMM") for short name 
Result: Nov
            =TEXT(B4,"MMMM") for long name format
Result: November

To extract year, however we can not extract the year name.
            =YEAR(B4)
 Result:  2017

To extract Time in HH:MM:SS Format, apply formula
            =TIME(HOUR(B4),MINUTE(B4),SECOND(B4))
Result:- 09.45.10 AM

To extract Date in DD-MMM-YYYY format
            =DATE(YEAR(B4),MONTH(B4),DAY(B4))
Result:  18-11-2017

To extract Week Number of the date falling the current year
            =WEEKNUM(B4)
Result:  46 this means the date which we used in formula as reference falls in 46th week on year 2017

To extract Week day of the date falling in particular Week.
            =WEEKDAY(B4,2) here 2 indicates that the week starts from Monday to Sunday
Result:  6 means Saturday is the sixth day on the week.

To extract same date for previous months or coming months.
1.            =EDATE(B4, -2)
              Here -2 tells excel to calculate the same date of 2 months back (September)
Result: 18-09-2017   

2.            =EDATE(B4,2)
               Here 2 tells excel to calculate the same date of next 2 months (January)
Result: 18-01-2018   


To extract last day of previous or coming month mentioned in formula.
1.            =EOMONTH(B4,-2)
              Here -2 tells excel to calculate the last date of 2 months back (September)

Result:  30-09-2017
2.            =EOMONTH(B4, 2)      
               Here 2 tells excel to calculate the last date of next 2 months (January)
Result:31-01-2018

Finding differences in two dates. This function is used widely. Lets see how we can get familiar with finding the differences.

I have two date in above table the first is 18-11-2017 and another is 28-01-2018

There are multiple date functions that we can use, however we need to apply as per our requirement.

1. DAYS(end_date, start_date)
           In this example end_date is 28-01-2018 and start_date is 18-11-2017
           =DAYS(B14, B4)
Result:  71
2. DAYS360(start_date, end_date, [method])
           In this example our first date would be oldest date and end date would the latest date
           =DAYS360(B4, B14)
Result:  70
           Here we can see the different results we get for same dates, this is because of DAYS function calculates the differences by considering 365 days in a year. However DAYS360 considers 360 days in year while calculating the differences.

Now let us calculate the number of working days, this means Saturday and Sunday's are considered as non-working days by default. This function is widely used while calculating TAT or AHT

NETWORKDAYS(start_date, end_date, [holidays])
            =NETWORKDAYS(B4,B14) mentioning holidays is optional as 2 non-working are by default
Result: 50

Now lets us think of designing table headers while developing a dashboard.

WORKING DATE 1	WORKING DATE 2	WORKING DATE 3	WORKING DATE 4
=TEXT(IF(TEXT(B4,"ddd")="Sat",B4+2,B4+1),"dd-mmm-yy")	=TEXT(IF(TEXT(B18,"ddd")="Sat",B18+2,B18+1),"dd-mmm-yy")	=TEXT(IF(TEXT(C18,"ddd")="Sat",C18+2,C18+1),"dd-mmm-yy")	=TEXT(IF(TEXT(D18,"ddd")="Sat",D18+2,D18+1),"dd-mmm-yy")
=TEXT(B18,"ddd")	=TEXT(C18,"ddd")	=TEXT(D18,"ddd")	=TEXT(E18,"ddd")

As shown in above table we are trying to show the dates in columns for designing dashboard 

Let us try to understand the formula I've used to get the dates

   =TEXT(IF(TEXT(B4,"ddd")="Sat", B4+2, B4+1),"dd-mmm-yyyy")

     Understanding step by step
 
      IF(TEXT(B4,"ddd")="Sat", B4+2, B4+1)
  
   Check if the reference date's day is Saturday and if so, then calculate date by adding 2 days to reference date, if not the calculate by adding 1 day to reference date

=TEXT(................, "dd-mmm-yyyy")

Show the calculated dates in dd-mmm-yyyy format.