Tags: INT

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

{6 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.

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 happen after every 24 months
4. Cell B7 - Rent per month, say 15,000

In cell B9, one may want to know the total rent payable over the tenure of the contact after factoring in the escalation clause.

You may refer to my solution in this workbook.

Calculate turn around time excluding Sundays and public holidays

{73 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.

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.