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...