Archives: Logical

Calculate hours between two cells

We needed that several times in the past Then I found it in an old file in my laptop, thought to share it for others to help So, when A1 has the start time, B1 has the end time Time different in one day will be from formula =IF(OR(A1=””,B1=””),””,ABS((HOUR(A1)+MINUTE(A1)/60)-(HOUR(B1)+MINUTE(B1)/60))&” hrs”)   I added to prevent • Read More »

Workdays – Across months

Get total number of days per month This was a question from one of my friends, This set of formulas will calculate how many days (Networkdays) in each of the months in the list for a given Start and End dates If Range of dates are across multiple years, user can copy columns C, D, • Read More »

Week number to Month number

Now we need to convert week number to month number This is exactly the same as week number to date (reverse weeknum) So, when the week number is in A5, then formula below will get you the month number

iframe in Excel (XLiFrame)

This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too. Formulas used to bring actual data is: =IF(OFFSET(INDIRECT(“‘”&$F$2&”‘!$A$1″),$D7-1,$D$6-6+COLUMN())=””,””,OFFSET(INDIRECT(“‘”&$F$2&”‘!$A$1”),$D7-1,$D$6-6+COLUMN())) While formula used to bring header labels:

Convert 2-column into wide (column-row) table

This Excel file will convert a table of two columns into a Column-Row table Using functions only and auto-updated once the Main table update In another word, analyze the table into wider form I needed this one few days ago and you will need this one too. The main formula that you need to use • Read More »