03. November 2015 · Comments Off on Workdays – Across months · Categories: Date and Time, Formulas - combined functions, STD f(x)s · Tags: , , , ,

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, E and F to fill those years

The months for the list are automatically populated starting from Jan of the year of the start date

 

The main formula in F7 should be:

=IF(OR($D$2>E7,$D$3<D7),"",
NETWORKDAYS(IF(D7>$D$2,D7,$D$2),IF(E7<$D$3,E7,$D$3)))

 

14. September 2015 · Comments Off on Extract current (Active) workbook name · Categories: Formulas - combined functions, Lookup and References, STD f(x)s, Texts and Strings · Tags: , ,

I need that more often than I thought

It is basically extract the full workbook name that we are in, with no folders, and with no extension


=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-6)

So, here it is….

 

And if we want to go 1 step further, I often have the version of the tool as part of the filename, to distenguish of the prior release, so below function will extract the version only, if we ever need to compare of the versions

=MID(CELL("filename",$A$1),SEARCH(" v",CELL("filename",$A$1))+2,SEARCH(".xl",CELL("filename",$A$1),SEARCH("[",CELL("filename",$A$1)))-SEARCH(" v",CELL("filename",$A$1))-2)

11. May 2015 · Comments Off on Week number to Month number · Categories: Date and Time, Formulas - combined functions, Logical · Tags: , , , , ,

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

=IF(A5<2,MONTH(DATE(YEAR(TODAY()),1,1)),MONTH(DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+((A5-1)*7)+1))

 

Enjoy

06. April 2015 · Comments Off on Why I ran from =INDIRECT() function · Categories: Formulas - combined functions, Lookup and References, STD f(x)s · Tags: ,

I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT()

Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things changed

Along with ADDRESS, ROW and COLUMN and some text tricks like SEARCH, LEFT, LEN, RIGHT, etc, you can do a lot.

You see INDIRECT does a simple job

Converts a text that represent a reference, into reference

So…

If you do

=INDIRECT("G4")

It gives you the value in G4

Notice that “G4” has the double-quotations, means

=INDIRECT("G"&SUM(S1:S4))

More »

06. April 2015 · Comments Off on Get column name (or headers or address as A,B,C, etc) as input inside cell · Categories: Formulas - combined functions, Lookup and References, STD f(x)s, Texts and Strings · Tags: , , , , ,

Excel already has the formula ROW()
=Row()
Which as we may already know will give us the number of the row we are in, if you add no parameters

4-6-2015 9-25-15 AM

However, the COLUMN() is kind of tricky

Because of the fact that cell addresses (or references) are based on the old style of R1C1

=Column()

More »

17. June 2014 · Comments Off on Week number to Sunday date – Weeknum reverse · Categories: Date and Time, Formulas - combined functions, STD f(x)s · Tags: , , , ,

This was a request from one of my work managers
She wanted to convert a week number she got in a column into thier dates

Excel already have the WEEKNUM that converts a date into its week number, but the reverse is what needed here

So after my mouth was drooling while she was asking her question, I started a head, and below is the result formula

You just need to place the week number in cell B3, then paste this formula anywhere:

=IF(B3<2,DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+((B3-1)*7)+1)

B2014-10-02WeeknumReverse

 

 

19. March 2012 · Comments Off on Fx to cut long column into two columns · Categories: Formulas - combined functions, Lookup and References, STD f(x)s · Tags: , ,

Convert one long column into two
This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column

This was the answer for this question
Yahoo! Answers question http://answers.yahoo.com/question/index?qid=20081225182043AAc7ZEB

Show two columns in one page as four columns using functions only. This will help you to show the next x number of rows for a certain column to the next of the first one to reduce the number of pages to print.

Can work also to convert into 3 columns out of 1, etc

Use this formula to grap the first column
=OFFSET(OneCol!$B$1,((INT((ROW()-1)/$A$1)+1)-1)*$A$1+ROW()-1,0)
And use this one to grap the second column
=OFFSET(OneCol!$B$1,((INT((ROW()-1)/$A$1)+1))*$A$1+ROW()-1,0)
Again, all these formulas are linked to cell A1

19. February 2012 · Comments Off on iframe in Excel (XLiFrame) · Categories: Formulas - combined functions, Lookup and References, STD f(x)s, Texts and Strings · Tags: , , , , ,

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:

=LEFT(ADDRESS(1,$D$6-5+COLUMN(),2),SEARCH("$",ADDRESS(1,$D$6,2))-1)

14. February 2012 · Comments Off on State Abbr with Index+Match+Validation · Categories: Formulas - combined functions, Lookup and References, STD f(x)s · Tags: , ,

Small file that will show how to do INDEX + MATCH formulas to get the abbreviation of the state based on its name or the name of the state based on its abbreviation

Main formula to do that is:

=INDEX(Abbr_States,MATCH(B4,States,0),1)

That is in addition to the Names created…

12. February 2012 · Comments Off on Zipcode-State search · Categories: Formulas - combined functions, Lookup and References, STD f(x)s, Texts and Strings · Tags: , ,

Enter a zip code, and Excel will tell you in which state it is
It also has a list of all zip codes for all states and a technique (INDEX and MATCH functions) to get the state from a given zip code.
Simple file to help learning INDEX and MATCH

The formula used is:
 =INDEX(Data!$D:$D,MATCH(TEXT(Search!C4,"00000"),Data!$C:$C,0),1)