Last Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel workbooks.
Quite a few people reading that page asked, “Wow, this is good. But how can I take a sheet full of =IF(ISERROR(…)….) formulas and convert them to =IFERROR()”
There is a different set of folks who asked “Wow, this is good. But quite a few of my colleagues use Excel 2003 and they see a bunch of #NAME errors when I send them an excel workbook with IFERROR formulas. Any help?!?”
I am pleased to announce that I wrote 2 simple macros, iferror2iserror() and iserror2iferror() that would scan formulas in a bunch of selected cells and convert them from IFERROR to ISERROR and vice-a-versa. Pretty cool, eh?
Download Excel Macros Workbook
Click here to download the workbook that has macros to convert IFERROR formulas to ISERROR formulas and vice-a-versa.
If you just want to examine the code:
Click here to view the VBA Module code.
What are these macros and how do they work?
The workbook contains 2 macros – iferror2iserror() & iserror2iferror().
What does iferror2iserror() macro do?
As the name suggests, It scans a bunch of selected cells for any IFERROR formulas and then converts them to ISERROR formulas.
For eg. if a cell has =IFERROR(expression, error), the output would be =IF(ISERROR(expression),error,expression)
What does iserror2iferror() macro do?
This macro scans a bunch of selected cells for any ISERROR formulas and then converts them to IFERROR formulas.
For eg. if a cell has =IF(ISERROR(expression),error,expression), the output would be =IFERROR(expression, error)
How to use these macros?
Very simple. Just select the cells with formulas and then run the required macro. The macros only affect cells with either IFERROR or ISERROR formulas.
What are the limitations of these macros?
These macros should hold good for many real life scenarios. That said,
- These macros do not check for IFERROR (or ISERROR) recursively. ie, if a formula has IFERROR inside another IFERROR, only the first one would be converted.
- These macros do not work when you have commas (,) inside the formula in double quotes. For eg. the below formula fails.
=IFERROR(VLOOKUP("Kirk, James",tblStarwars,2,false),"Captain not found"))
Your comments:
How do you convert IFERROR or ISERROR formulas? Do you use a macro or you manually change the formulas? Please share your techniques and ideas using comments.
Also, if you wish to modify the code, please feel free to do so. Share your work with rest of us thru comments so that we can benefit too.
13 Responses to “Convert fractional Excel time to hours & minutes [Quick tip]”
Hi Purna..
Again a great tip.. Its a great way to convert Fractional Time..
By the way.. Excel has two great and rarely used formula..
=DOLLARFR(7.8,60) and =DOLLARDE(7.48,60)
basically US Account person uses those to convert some currency denomination.. and we can use it to convert Year(i.e 3.11 Year = 3 year 11 month) and Week(6.5 week = 6 week 5 days), in the same manner...
This doesn't work for me. When applying the custom format of [h]:mm to 7.8 I get 187:12
Any ideas why?
@Jason
7.8 in Excel talk means 7.8 days
=7.8*24
=187.2 Hrs
=187 Hrs 12 Mins
If you follow Chandoo's instructions you will see that he divides the 7.8 by 24 to get it to a fraction of a day
Simple, assuming the fractional time is in cell A1,
Use below steps to convert it to hours & minutes:
1. In the target cell, write =A1/24
2. Select the target cell and press CTRL+1 to format it (you can also right click and select format cells)
3. Select Custom from “Number” tab and enter the code [h]:mm
4. Done!
Hi, sorry to point this out but Column C Header is misspelt 'Hours Palyed'
good one
So how do I go the other way and get hours and minutes to fractional time?
If you have 7.5 in cell A1,
- Use int(A1) to get the hours.
- Use mod(A1,1)*60 to get minutes.
If you have 7:30 (formatted as time) in A1
- Use hours(a1) to get hours
- Use minutes(a1) to get minutes.
I had the same issue. You can solve it by changing the format as described above:
Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm
([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)
Thanks guys, these are the tips I'm looking for.
...dividing the number of minutes elapsed by the percent change is my task - "int" is the key this time
It doesnt work for greater than 24 hours
It returns 1:30 for 25.5 hours. It should have returned 25:30
Ideally I would right function as
=QUOTIENT(A1,1)&":"&MOD(A1,1)*60
Sorry, replied to wrong comment....
----
I had the same issue. You can solve it by changing the format as described above:
Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm
([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)
Clever use of MOD here to extract the decimal part of a number. Divide a number containing a decimal by 1 and return the remainder. Humm. Very clever.
Thanks very much, extremely useful !