Resolving #NUM error in excel

I was using excel to display data from a cube. I tried to do a caluclated cell in excel to display number of months between two dates. Here is the formula I used to do this.

=Datedif(H1,G5,"m")

But I received an error -- #NUM

Then I again looked at the dates entered. To make sure that the datedif function works you need to give the earlier date as the first parameter and the later date as the second parameter to avoid the #NUM error. You also need to ensure that the dates are in the correct format otherwise you will receive a #VALUE error.

So to avoid the #NUM error I changed the formula to

=Datedif(G5,H1,"m"). This retunrs the difference between the two dates in months.

24 Hours of PASS --March 2012 Registrations Open

This morning I recieved the email of 24 hrs of PASS registration. Below are the details.

Nonstop SQL Server Training Don't miss the best 24 hours of free, online SQL Server training in the industry with 24 Hours of PASS: SQL Server 2012 March 21, featuring closed captioning in 15 languages. Join us for an exceptional lineup of the world's top SQL Server and BI experts, who will be presenting 24 back-to-back technical webcasts with a special focus on SQL Server 2012.

Go ahead and register and benefit from these sessions.

Have you heard of Prezi?

Late last year one of my colleagues Mark Crosby has introduced the Prezi tool in our company to do presentations. Prezi is a presentation software that can be used in creating more visual presentations. It is different to the traditional powerpoint software as it has a Zooming user interface and uses a vector based illustration and text which creates big visual impact.

The main differences between PRezi and Powerpoint that I have found are as follows:

The first difference I found is that Prezi is an online application that does not need any installation of software whereas Powerpoint is an installed software application.

Prezi is more like a canvas based presentation whereas powerpoint is more based on slides that are arranged in a sequntial manner.

In Prezi you cannot just copy and paste pictures like we do in powerpoint. The pictures need to be uploaded and then included in the Prezi creations.

In Prezi you can use only flash objects and embed youtube videos whereas in powerpoint you can include a wide variety of video formats.

You cannot print the sides as a handout in Prezi like you do in powerpoint.

There are many other differences, strengths and weaknesses in both the tools and for now I will start using PRezi more and more and see as it is easy to learn.

Let me know what your thoughts are

Automatcially download pictures in Outlook 2007

Today I was asked by my colleague as to how to change outlook 2007 so that she does not manually need to download the pictures every time an email comes in.

Here is what I have suggested to her.

• Go to the menu item Tools in Outlook and then click on Trust Center


• Then choose Advanced settings


• Untick the "Don't Download pictures automatically' option as shown below

Have you heard of space function?

Today I had a unique scenario where I had to introduce 2 spaces in between two fields.

I came across the space function which I used in my scenario.

The syntax is space(integer_expression)

The integer expression is an integer. IF a negative value is supplied a null is returned.

So for example look at the following syntax:

select First_name, + space(4) + Last_Name from test_table

The above select statement will generate 4 spaces in between the first name and last name

The space function will generate a miximum of 8000 spaces.
For more information click here

Multiply and Divide a group of cells in Excel

I just now realised that it has been more than a month since I have posted anything on my blog. So here I am with the most recent tip I learnt in Excel.

If you have a column of cells that need to be divided by 1000 for example. Here are the steps that you need to follow.

• Type 1000 in a cell lets say A2



• Copy the cell A2 using Ctrl+C



• Select the cells in the column where you would like to let us say multiply the values by 1000.



• Right click and select Paste Special



• Click on Multiply as shown below.

You can do the same for adding or subtracting a specific value from a group of cells.

This tip is particularly useful if you want to show the data that is in millions but want to reduce the number of digits by dividing them by 1000.

Hope this heps.

Increasing the number of recent files in SSMS

In Sql Server Management Studio, I usually access my sql query files that I save -- using the File -- Recent Files option. The default setting of the number of recent files to be displayed in SSMS. Today I wanted to access a file that I had used last week and I could not find that file in the recent list.

So I thought of displaying more than the normal 4 files that are displayed in the Recent Files option. So I set out to find where the settings are (as in the microsoft office programs) -- I clicked on Tools -- Options -- General -- The option reads as

Display 4 files in the recently used list. I changed the number 4 to 10.

Now it is starting to display 10 recently used files.