Tags: INT

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

{ 25 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 […]

Read More →

Compute rent payable over the contact period after factoring in escalations

{ 0 Comments }

Assume the following data on a worksheet: 1. Cell B3 – Tenure of loan (in years).  This will be a whole number, say 15 2. Cell B5 – Escalation (in percentage), say 10% 3. Cell B6 – Escalation period (in months), This will be a whole number, say 24.  This means that the escalation will […]

Read More →

Calculate turn around time excluding Sundays and public holidays

{ 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 →

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 […]

Read More →