Sample Solutions



Example 1

In Excel 2003, When producing Schedules - there is no simple built in function that shows the next end of the month date without typing it in - each time.
Desired Result
     January 31, 2007
      February 28. 2007
      March 31, 2007
      April 30, 2007


My Solution
      Type in Cell c3: 9/30/2007
      Type in Cell c4: =DATE(YEAR(C3),MONTH(C3)+2,1)-1
      Then copy down from C4 to C5:C14
      Then format C3:C14 as date (pick format from list)


      Option To Show end of Quarter Dates
      Type in Cell C4: =DATE(YEAR(C3),MONTH(C3)+4,1)-1



Example 2
In Excel 2003, A company had a service contract that required work to be 80% of the calls to be resolved within 4 hours (from 9-5 Excluding Weekends). They had an excel spreadsheet showing date and time of call and completed date and time. Failure to complete 80% of the call within 4 hours resulted in substantial Penalties

Desired Result: Show calls completed late


Call Date Call Time Due Date Completed Date Completed Time Late Weekend/Holiday
B C D E F G H
9/30/2007 9 9/30/2007 13 9/30/2007 12 No No
9/30/2007 15 10/1/2007 10 9/30/2007 16 No No
9/30/2007 15 10/1/2007 10 10/1/2007 9 No No
9/30/2007 15 10/1/2007 10 10/1/2007 15 Yes Friday
10/2/2007 15 10/3/2007 10 10/4/2007 9 Yes Saturday

      (D) entered formula to calculate due date and time
          =IF(C2+4<17.1,B2+(C2+4)/24,B2+(C2+20)/24)

      (G) entered formula to calculate if late
          =IF(D2< (E2+F2/24),"Yes", "No")

      (H) entered formula to check if Weekend/Weekday
          =if(G2="Yes",vlookup(D2,A$600:B$610,2,False),"No")
          =Day of week is checked Only if Late=yes

          Quick Validation: if #late/Total-Calls < 20% then no additional checks needed
          Full Validation: if #late/Total-Calls >= 20% Weekday then additional manual check for Friday, Saturday and Sunday



Example 3
I received a File that contained Names, addresses and telephone numbers that were not formatted

I created a Function in Visual Basic to clean the telephone number using one format to eliminate the Duplicate Records.
     

Original Phone Number Cleaned Phone Number Comment
516-555-1234 516-555-1234 No cleaning is needed
(631) 544-5120 631-544-5120
1-718-544-8345 718-544-8345
ESP-334-5120 377-334-5120
7435120 000-743-5120
1-Esp-334-8345 377-334-8345
5445555-Ext 2010 x-5445555-Ext 2010 Requires manual cleaning
5445555- 000-544-5555


                 Click Here To See VBA Code



Once the data was cleaned, I sorted the data, then, from the Excel Menu

   I used Data
      /Filter
         /Advanced Filter
            /Unique
   to eliminate the duplicates

 

I started with about 17,000 records and eliminated about 1,500 duplicates.



Example 4
Sorting By more than 3 columns
      Excel 2003 only permits sorting by up to 3 columns
      This has a fairly simple solution: Add a new column (F) and enter a formula in F2, then copy downward from F2.
      The primary sort is by Last Name (Col A), the secondary sort is by First Name (Col B), then the 3rd sort is by Col F

       The formula in F2 is: =C2&"-"&D2&"-"&E2

   


 

 

 

 



Feel free to contact me at Natalie@FunctionsToGo.com    Or at 631-544-5120