Add-in and macro to convert seconds to hh:mm:ss

This add-in and macro will help you in converting seconds to hh:mm:ss. I have put add-ins for those who just wants to use the add-ins and macro for those who wants to understand this trick. This is very useful in BPO/Contact Center where you have source data in seconds and you have to represents data in hh:mm:ss seconds.

Mentioned below is macro code

Sub convert_hh_mms_ss()
Dim i As Double
Dim x As Integer
Dim y As Long
x = 1
y = 1
For Each c In Selection
    i = c / 86400
    Selection.Cells(x, y) = i
    Selection.Cells(x, y).NumberFormat = "[h]:mm:ss;@"
    x = x + 1
Next c
End Sub

Logic: c is each cell in selection/range on which you want to perform conversion. 86400 is nothing but the number of seconds in a day ( 24*60*60). When you divide seconds(c/86400) with 86400 it returns the numeric value in which MS Excel stores the time. In next steps we convert this numeric value into time format.

If you have difficulty replicating the code in MS Excel. Please download the example file.

Download Macro Example

Also, if you can use this macro as Add-in. Dow

Download Add-ins

If you need help with installation of Add-ins, please follow our earlier post

How to install Add-ins

Please do let me know your comments about this macro and add-ins. Also, you can subscribe via email to receive latest updates, add-ins and e-books in your inbox.

We assure you knowledge, not SPAM!

Read more on this article...

Remove password

Few days back I wish to remove the password from protected file. So, I started hunting for password removers for MS Excel in Google. I found a unique utility to quickly remove the password which I wanted to share with you all. The reason I am posting this here is there are many such utility available and few of them are paid as well. Also, I have already tested it by putting strong passwords.

Download Password remover Add-in

This utility is in the form of Add-in. So, click on the link below to follow the installation instruction.

How to install MS Excel add-ins

I would like to convey the special thanks to the developer. Kindly visit them as well

http://www.straxx.com/excel/password.html

Kindly let us know your comments about your experience about using this add-ins. To receive more such add-ins, e-books and MS Excel updates subscribe to us via email

Disclaimer: This add-ins is not developed by me, nor I am anyway associated to the developer. To view developer site click here

We assure you knowledge, not SPAM!

Read more on this article...

VBA to close excel.exe

Closing all orphan Excel.exe process

I came across this issue while I was generating reports on SQL server using MS Excel. Here we use to generate reports using MS Excel macro. However this macro's where executed via DTS in SQL. At times if any error occurs, the DTS use to close down leaving excel.exe running as orphan object. This not only sometimes prevent other excel objects but also slow downs your server performance. To tackle this we used the following code to close all MS Excel objects before opening new object.

Sub Close_Excel()

Dim strClsExl As String
strClsExl = "TASKKILL /F /IM Excel.exe"
Shell strClsExl, vbHide

End Sub

Warning: Kindly save all your work before trying above code as this will close all MS Excel instances running on computer and you will end up loosing your work.

For your convenience I have linked two files for download along with this post. One has Macro example which you can in understanding code while other is an add-in which will can directly install in your MS Excel. Also, in add-in I have let user to decide which .exe file they want to close.

Download Close Excel example

Add-ins to close exe

If you need more help with installing add-in on your computer do visit our other post which is mentioned below.

Install Add-ins help

Kindly leave your comments about this post and receive more such add-ins by subscribing us.

We assure you knowledge, not SPAM!

Read more on this article...

Weekday function

This function is used to find day of week from Date. Also, you can decide which day of week you want to begin the week. Like for different organizations have different week beginning and ending. We will first understand syntax  and than move on two unique use of Weekday function.

Syntax: = Weekday(DateEntered, WeekStart)

WeekStart =1 sets Sunday as start that is 1  and Saturday as end which will7

WeekStart = 2 sets Monday as start that is 1  and Sunday as end which will be 7

WeekStart = 3 sets Tuesday as start that is 1 and Monday as end

If you don't provide any WeekStart then by default MS Excel assume it to 1.

Take a look at image below with weekday formula. 

Click on image below to view enlarge

Example 1: We want to find out day name using weekday function.

Note: The cell format should be choosen as dddd in custom formatting. To choose cell formatting, Press Ctrl + 1 and select custom from category and type dddd.

Example 2: Format entire rows where day is Monday. This is very useful when your week start/end at Monday and you want to mark start or end of week.

Click on image below to view enlarge

 

Here the entire row is formatted based of the value present in A column. If you still have confusion, download the file with Weekend function.

Download Weekday function Example

Also, do let me know your comments about this post and subscribe to us to receive latest updates via email

We assure you knowledge, not SPAM!

Read more on this article...

INT function

INT rounds of the number to lowest and nearest whole number. INT function does not require any special formatting.

Syntax: INT(Number)

Number	Integer	Formula
3.5	3	=INT(3.5)
7.2	7	=INT(7.2)
15.99	15	=INT(15.99)
-22.7925	-23	=INT(-22.7925)

Example: A competitive exams require the 18 years of age to appear in exams.  Here is example on finding candidate with age over 18.

Click on image to view enlarge

I have uploaded the MS Excel file with INT function and INT function example to ensure that we don't miss anything with respect to basic understanding of INT function.

Also, do let me know your comments about this post and subscribe to us to receive latest updates via email

INT function Example

We assure you knowledge, not SPAM!

Read more on this article...

Rand and Randbetween function to generate random numbers

Rand and Randbetween is used to generate random numbers. It is very useful for people like me who post on blog. This help me in generating random data which I use in post on blog. Randbetween is available in MS Excel 2007 while RAND function is available in MS Excel 2007/2003/XP/2000. Randbetween function has an edge over Rand function as it can generate numbers between lower bound and upper bound and gives user a control to over generation of random numbers.

Rand() Function

Syntax: Rand()

RAND() function does not require any special formatting. However it returns number in decimal or in others words it returns you number between 0 to 1. Take a look at the image below with example of RAND() function.

Click on image  to view enlarge

Randbetween() function

Syntax: RANDBETWEEN(LOWER, UPPER)  is syntax for RANDBETWEEN() function in MS Excel 2007.

Like Rand() function, Randbetween() function does not require any special formatting. Take a look at image below to view example of Randbetween() function.

Click on image  to view enlarge

 Example: The example exhibits how to use RAND() function to generate numbers between fix range.

Click on image  to view enlarge

Mentioned below is RAND() function along with INT function and is equivalent to RANDBETWEEN function of Excel 2007. 

Formula: INT(RAND()*(b-a)+a)

here a= 100 and b=200

=INT(RAND()*(200-100)+100)

(200-100) returns 100 which when multiplied with RAND() return two digit number. Also, here it is treated as upper bound and lower bond. In other words, this function will return numbers between 100 and 200. You can try with different numbers.

RAND()*(200-100)+100 adds to 3 digit number which is greater than 100 but less than 200. Also, some decimal.

INT functions round off to lowest integral value.

If you are still in doubt using RAND() and RANDBETWEEN() function. I have kept the file with example for download

Random Number Example download

To receive more updates on MS Excel help. You can subsribe via email.

We assure you knowledge, not SPAM!

Read more on this article...

Product function

This is simple multiplication function. Very useful in almost all worksheet. However, we prefer to use other alternative to multiply numbers. The alternative to Production function is using cell reference in following manner:  A1*B1*C1 which is equivalent to Product(A1,B1,C1)

Syntax

=Product(number1, number2, number3........,number30)

=Product(Rangeofnumbers)

=Product(number, Rangeofnumbers)

Click on image below

You can download the file with Product formula example. I am sure this will help you in learning this function.

Download Product Example File

Please do let me know your comments about my attempt to help beginners with Product function. Also, you can subscribe via email

We assure you knowledge, not SPAM!

Read more on this article...

Shortcut key to enter Date and Time in worksheet

Using NOW function refreshes each time when you make entry to worksheet. Hence, I dont prefer to use NOW function. I use shortcut key to enter time or Date to worksheet.  This trick is helpful in saving time.

For Time

Shortcut key: Ctrl + Shift + ;

For Date

Shortcut key: Ctrl + ;

Please do let me know your comments about my attempt to help beginners with Shortcut key to enter date and time. Also, you can subscribe via email

We assure you knowledge, not SPAM!

Read more on this article...

Now function for Time

Now function is used to enter current date and  time. The Now function keeps on updating time whenever you open workbook or when you make entry to worksheet. This function is useful when you want to use current date and time.

Formula: Now()

Now function returns date and time. However the format of cells plays very important role. Click on the image below to take a look of different cells formatted returns date and time in different date and time formats. In simple words, Now functions returns date and time based on cell format.

Click on image to view enlarge

For further help, you may download MS Excel file with Now function example.

Now Function Example download

Please do let me know your comments about my attempt to help beginners with Now function. Also, you can subscribe via email

We assure you knowledge, not SPAM!

Read more on this article...

SUMPRODUCT function

The SUMPRODUCT in MS Excel is useful when you want a multiple a set of values to another set of value. This formula is very useful in scenario when you want to calculate the total sales value or cost. Let's take look at syntax.

Syntax: SUMPRODUCT(array1, array2, array3....)

Table

Set A	Set B
a1	b1
a2	b2
a3	b3
a4	b4
.	.
an	bn

SUMPRODUCT({a1, a2, a3, a4...an}, {b1, b2, b3, b4...bn}) = a1 X b1+a2 X b2+a3 X b3+a4 X b4....an X bn

Click on image below to Enlarge

 

In the above example, we have describe an example to calculate Total sales value and Total Cost value. Also, I have enclosed the link with example of SUMPRODUCT formula.

Click here to download SUM Product Example

Kindly let me us know your comment about this post. Also, you can subscribe to this blog to receive latest update in your inbox. Click on the link below

Subscribe to blog via Email

We assure you knowledge, not SPAM!

Read more on this article...