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 […]
Read More →
{ 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 […]
Read More →
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 […]
Read More →
{ 206 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 […]
Read More →
{ 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 […]
Read More →
{ 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 […]
Read More →
The DATEDIF() bug
{ 17 Comments }