PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
BEFORE READING THIS ARTICLE , IT IS REQUESTED TO VISIT THE PART I WHICH DISCUSSES THE CONCEPT OF THE DATE AND TIME IN EXCEL FOR BETTER UNDERSTANDING.CLICK HERE TO VISIT.
DATEDIF FUNCTION CALCULATES THE NUMBER OF DAYS , MONTH OR YEARS BETWEEN THE TWO SPECIFIED DATES.
This function calculates the age if we provide it by a starting date, an
ending date and the output type[ the form in which we need the result.
It can be years, or months or days etc.]
The Syntax for the DATEDIF function in Excel is
=DATEDIF(START_DATE, END_DATE, OUTPUT_OPTION)
START_DATE is the starting date from which the counting would start or the starting date of the period. This argument needs to be given in “” or as a date number. It can also be a result of any function resulting in Date.
END_DATE is the ending date till which the counting would be done or the end date of the period. This argument should also be given in “” or as a date number.
OUTPUT_OPTION is the unit in which the result is required. Refer the following table.
Use the option as per the result required.
The type of information that you want returned, where:
OPTION
“Y“
The number of complete years in the period.
“M“
The number of complete months in the period.
“D“
The number of days in the period.
“MD“
The difference between the days in start_date and end_date. The months and years of the dates are ignored.
This option is known to have issues which will be discussed later.
“YM“
The difference between the months in start_date and end_date. The days and years of the dates are ignored
“YD“
The difference between the days of start_date and end_date. The years of the dates are ignored.
Let us find out the exact age of any person in Excel.
Say, the date of birth of a person is 21-06-2000
Find his exact age today i.e. on 2-07-2020
STEPS TO FIND OUT THE EXACT AGE
If this is the only requirement , i.e. if you want to find out the number of days between the two dates, we can always use the simple difference between the dates.
As we know that DATE is actually a number by itself. If we simply subtract the earlier date from the later one, it’ll provide us the number of days.
Suppose we have a date in cell H5 as 02-07-2020 and the other date is I5 as 30-05-2019.
We will find the number of days between H5 and I5 in J5 by putting the formula as
=H5-I5
But this function will create error if we try to subtract the date which comes late from the other one.
IF WE JUST NEED THE DIFFERENCE IN DAYS IRRESPECTIVE OF THE START DATE OR END DATE
ABS(START DATE-END DATE) OR ABS(END DATE-START DATE)
ABS function doesn’t mind with the negative values and makes everything positive. It is the same function as ABSOLUTE in mathematics.
USE THE FUNCTION AS
=ABS(H5-I5). The answer will be 399
or
=ABS(I5-H5). The answer will be 399
No errors.
WHENEVER WE FIND THE DIFFERENCE IN DAYS , WE SHOULD ALWAYS TAKE CARE OF THE DAYS TO BE CONSIDERED. SOMETHING LIKE, IF DAY 1 IS SUBTRACTED FROM DAY 2, THE MATHEMATICAL RESULT WILL BE 1 BUT IF WE WANT TO CONSIDER BOTH THE DAYS WORKING, WE NEED TO ADD 1 I.E. DIFFERENCE+1. SO ALWAYS BE CAREFUL ABOUT THIS.
For the example , we will take the standard working week i.e. from MONDAY TO FRIDAY with SATURDAY and SUNDAY OFF.
STEPS TO FIND OUT THE STANDARD WEEK START DATE AND WEEKEND DATE OF THE CURRENT WEEK
Adding the time is again a very important utility in excel but if we don’t know the proper way to handle it, it can create problem for us.
Let us try to find out the different ways to find out the sum of time in Excel.
WHENEVER WE TRY TO PERFORM OPERATIONS ON DATE OR TIME, ALWAYS REMEMBER THAT THEY ARE NUMBERS AND TRY TO PERFORM CALCULATIONS ON NUMBERS FIRST.
EXAMPLE 3: Let us put all the complications in this example and try our formula if it still works or not.
TIME 1 | 11:53 |
TIME 2 | 12:59 |
TIME 3 | 14:59 |
TIME 4 | 06:30 |
TIME 5 | 10:15 |
TIME 6 | 16:15 |
TIME 7 | 32:20:00 |
TIME 8 | 64:40:00 |
We use the following function for this.
=TEXT(SUM(F34:F41),”[HH]:MM”)
Where F34 to F41 has all the time stored which is summed up.
When it is summed up, it’ll change the date after every 24 hrs but as we want the result in hours only we use the TEXT FORMULA and give the format as [HH]:MM which will give the total hours and minutes as answer.
Time is just another number for Excel. We can subtract it the same way as numbers but with a bit care.
Just like the addition, we can simply subtract the time arithmetically. For example
If a cell contains 12:00 and one contains 4:00 we can simply subtract the 12:00-4:00 and the result would come as 8:00
The formula used is D8-E8.
Let us try the different cases and learn how to tackle different situations.
EXAMPLE 1: SIMPLE DIFFERENCE BETWEEN TIMES.
TIME 1: 15:00 HRS
TIME 2: 8:00 HRS
DIFFERENCE= TIME 1- TIME 2= 7:00
As shown in the picture below.
EXAMPLE 2: SUBTRACTING THE TIME DURING NIGHT HOURS
This particular case is for certain situations where we are calculating the work hours for a night shift.
Mostly, the night shift starts on the day 1 and ends in the morning of day 2.
This is the situation where our simple subtraction fails.
Let us try an example where the night shift starts at 10 pm and ends at 7 am.
Let us try to find out the work hours.
We put the formula as SHIFT END TIME-SHIFT START TIME [C15-B15 FOR OUR EXAMPLE] but the result didn’t show and #### appeared.
It appears when the result is negative.
WHEN WE DOESN’T MENTION THE DATE WITH A TIME, THE DEFAULT DATE IS TAKEN WHICH IS JAN 1 , 1900. ThE TIME DOESN’T GO WITHOUT A DATE WHETHER YOU MENTION IT OR NOT.
Let us try to sort this problem out.
STEPS TO FIND OUT THE TIME DIFFERENCE INCLUDING NIGHT HOURS [NIGHT SHIFT CASE]
We will discuss two ways to calculate the decimal part.
2. MANUALLY FINDING THE DECIMAL EQUIVALENT OF TIME.
The current time calculation is quite easy in Excel as we already have the functions which provide us the current time.
We can make use of Now() function which gives the current date and time altogether and Today() function which gives the current date only.
Now as we know that we need the current time only, we can subtract Today() from Now(), the integral part would be subtracted and we would be left with the decimal part only which is the time.
STEPS TO INSERT CURRENT TIME IN EXCEL
TIME FUNCTION BECOME VERY IMPORTANT WHEN WE HAVE TO TREAT HOUR, MINUTE OR SECONDS INDIVIDUALLY AND THEN PASS THEM AS A TIME VALUE.
EXAMPLE 2: CREATING TIME FROM TEXT.
This is a specific case when we have copied any data from other source and it behaves as TEXT.
Many times we import or copy the data from other sources and all the imported data behaves as Text. It means that we won’t be able to apply any kind of calculations on that particular data as TEXT won’t respond to any calculations.
Let us take an example to understand the same.
We have the following scenario. We copied a list of dates from somewhere which are in the text format.
PLANNING FOR THE SOLUTION:
Now, if we try to sort this problem out by changing the format, it is not going to work.
But we can make use of TIME FUNCTION and separate the hour, minute and seconds parts using the LEFT, RIGHT , MID functions and pass the arguments to TIME FUNCTION. It will work as the data will be converted to time.
STEPS TO CONVERT THE TEXT INTO TIME:
EXPLANATION:
We are simply taking out the text snippets and passing it to the TIME FUNCTION.
Let us try to understand the formula.
=TIME(IF(LEN(AB9)=5,LEFT(AB9,2),LEFT(AB9,1)),MID(AB9,LEN(AB9)-4,2),RIGHT(AB9,2))
The formula explanation from the left.
The outer function is the TIME FUNCTION which takes the three inputs as HOUR, MINUTE and SECONDS.
We extract the HOUR from the LEFT FUNCTION. Now we can check our data that we need to decide whether we ‘ll take the one digit or two. For this case we put the IF condition which will act as per the total number of LENGTH of the string.
The second input is the MINUTES, which we extract with the help of MID FUNCTION. The MID FUNCTION starts from the length -4. The number of characters to the right are fixed but from the left it is not fixed due to the number of digits in the HOUR FIELD. So we extract it from the right and subtract the fixed position which is 4 and extract 2 letters.
The third and final extraction is easier so we use the RIGHT FUNCTION and get the last two letters.
The output is correct. The text time are converted into TIME VALUES WITH CORRECT FORMAT.
THE SAME PROBLEM CAN BE SOLVED USING THE TEXT FUNCTION TOO. BUT THE ABOVE METHOD IS MENTIONED INTENTIONALLY AS IT GIVES US MUCH MORE FLEXIBILITY AND COVERS OTHER SOLUTIONS TOO.
CONVERTING THE TEXT TO TIME USING TEXT FUNCTION:
We can solve this problem using TEXT FUNCTION too.
STEPS TO CONVERT TEXT TO TIME USING TEXT FUNCTION.
A situation can arise where we need to add some time[in hours, minutes or seconds] directly to the given time.
We can add this time by making use of the TIME FUNCTION.
For Example
Suppose we have time in cell D4 and we need to find out the time after 3 hrs 15 minutes.
Follow the steps to find the time after certain hours, minutes and seconds.
Many times the time is divided into slots and we need to take the working time from the slot only and not before that.
In such case we need to round the time to the nearest slot.
Suppose we have a situation in which the time would be taken from the next 15 min slot for example if the activity starts at 10:10 the effective time would be from 10:15.
The effective timings will be every 15 min, e.g. 10, 10:15, 10:30, 10:45 and so on.
STEPS TO CONVERT THE TIME INTO SLOTS.
EXPLANATION OF THE FORMULA USED:
=TIME(HOUR(E17),CEILING.MATH(MINUTE(E17),15,0),0)
We started with the outer function TIME, which would take three inputs as HOUR, MINUTES and SECONDS.
HOURS are extracted simply using the HOUR FUNCTION which will give the number of hours from the given time.
As we need to round off the minutes, we use the function CEILING.MATH in which we put the inputs and have given the significance of 15 minutes, seconds are zero.
Now any time would be rounded to the next 15 min slot.
MILITARY TIME is expressed as the 24 hour time but without the colon [:].
For example 1 AM as 0100 and 10:30 pm AS 2230. So the time ranges from 0000 to 2359. Let us find out the conversion of NORMAL TIME to MILITARY TIME and MILITARY TIME to NORMAL TIME.
CONVERT STANDARD TIME TO MILITARY TIME:
We can convert it simply using the TEXT FUNCTION.
STEPS TO CONVERT STANDARD TIME TO MILITARY TIME.
CONVERT MILITARY TIME TO STANDARD TIME
We have the following challenges before we convert the military time to standard time in Excel
STEPS TO CONVERT MILITARY TIME TO STANDARD TIME.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE