Category: DATE AND TIME

The DATEDIF() bug

{17 Comments}

Assume a beginning date in cell A7 and ending date in cell B7.  In range C7:E7, the task is to compute the years (in cell C7), remaining months (in cell D7) and remaining days (in cell E7).  As an example, if one types in 12-March-2013 in cell A7 and 24-March-2014 in cell B7, then the result in range C7:E7 should be 1,12 and 12 respectively.  The formulas for getting these results are:

1.  In cell C7 - =DATEDIF($A7,$B7,"y")
2.  In cell D7 - =DATEDIF($A7,$B7,"ym")
3.  In cell E7 - =DATEDIF($A7,$B7,"md")

So far so good.  All the results are correct.  However, there is a problem with the "md" syntax of the DATEDIF() function.  Try this

1. In cell A7, enter 31-July-2007
2. In cell B7, enter 02-March-2009
3. In cell C7, enter =DATEDIF($A7,$B7,"y")
4. In cell D7, enter =DATEDIF($A7,$B7,"ym")
5. In cell E7, enter =DATEDIF($A7,$B7,"md")

The result in cell E7 will be -1 which is clearly incorrect.  To get the correct result of 2 in cell E7, try this formula instead

=B7-EDATE(A7,(C7*12)+D7)

Hope this helps.

Filter on a column of Date and time values

{4 Comments}

Assume a three column database - Site ID, Reason & Date and time.  The data is available in range A2:C6 of sheet1 of this workbook.  The objective is to view all those records where Mains failed between 23:00 and 03:00 (irrespective of the date).

Applying a Custom Filter > Between on the Data and time column (column C) with a value of 23:00 in the "Greater than or equal to" and 03:00 in the "Less than or equal to" would be incorrect because dates are also present in the same column.  The other option would be to use Data > Text to Columns to separate Date and Time into two columns.  If one then applies the same custom filtering criteria on the Time column, nothing would show up - this is because MS Excel will understand 23:00 as greater then 03:00.  If one reverses the custom filter criteria i.e. "Greater than or equal to" as 03:00 and "Less than or equal to" 23:00, the incorrect results would show up.

The solution is to solve this via Advanced Filters.  The steps for solving are mentioned in the file.

Derive end date and time from start date and time, office working hours and lunch breaks

{23 Comments}

Given the following inputs/restrictions, one may want to compute the end date and time of a project:

1. Start date and time of the project; and
2. Official working hours; and
3. Lunch breaks hours

Furthermore, to add to the complication, one may have different National holidays and weekend days i.e. while for some, the weekend days may be Saturday and Sunday, for others it may be Friday and Saturday.

You may refer to both my solutions here

1. Given National holidays and where Saturday and Sunday are the weekend days - Download workbook.  This solution will work in all Excel versions

2. Given National holidays and where Friday and Saturday are the weekend days - Download workbook.  This solution will work only in Excel 2010 and higher versions.

Calculate turn around time excluding Sundays and public holidays

{186 Comments}

Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell).  Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays.

You may refer to my solution in this workbook.

Determine weekday after factoring in user specified holidays

{0 Comments}

Given a certain beginning date and specified number of days, one may want to determine the ending date (beginning date plus number of days).  When determining the ending date, the following two cases should be considered

Case 1

Holidays are:

1. Sundays only; and

2. User specified public holidays

Case 2

Holidays user specified public holidays only.  Saturdays and Sundays are working days

If the landing date is any of the two holidays, the result should be the next working day.

You may refer to my solution in this workbook.

Validate cell to accept current time

{0 Comments}

To enable a person to enter current time in a cell, enter the following formula in Data > Validation > Custom

=ABS(A1-(NOW()-INT(NOW())))<(1/(60*24))

In this validated cell now, a person can enter time either by pressing Ctrl+Shift+; or by simply typing in the time entry.  The validation rule is such that it will accept only time which is within a minute of current time.  Change the 1 in the formula to change the 1 minute logic.