Keeping track of your expenses is one of the fundamentals of living good life. So I asked you to prepare a personal expense tracker as part of our 10,000 RSS Subscriber Milestone contest. I have received 7 excellent entries in this contest, each capable of making expense tracking a breeze while providing good analytics of the expense data to understand how you spend.
Thanks everyone for participating and making this a huge learning experience for you and I. Personally I have learned several useful formula and tracker related tricks from this.
How to vote?
Each of the 7 entries start with a title including authors name. Each entry includes a small image of the tracker along with few other thumbnails. Click on the images to see them in bigger versions. You can download the source workbooks and play with the trackers yourself.
Tell me which one you liked most by posting a comment with the option number. The winner (option getting maximum votes) will get Toshiba Mini 300 Series Netbook.
That is right. A Netbook. (find out more about the exact model and specs here
Please note that these files are copyrighted to original authors and you cannot use them for commercial purposes.
I have included 3 comments against each entry based on my understanding of the tracker. Please share your opinions and reviews using the comments section of this post.
Excel Personal Expense Tracker by Bigtaff [Option 1]
My comments:
- Looks awesome
- Can handle multiple currencies
- Provides excellent analysis on various criteria (by month, monthly, annual, by person, by category etc.)
Excel Personal Expense Tracker by Cnat [Option 2]
My comments:
- Very cool frequency analysis of expenses by date
- Good use of in-cell charts to compare income with expenses
- Simple, easy to use tracker
Excel Personal Expense Tracker by Ibrahim [Option 3]
My comments:
- Separate sheets for each of the 12 months, good for yearly tracking
- Analytics by month or by expense category
- Simple Grid like structure for entering data
Excel Personal Expense Tracker by Karthik [Option 4]
My comments:
- Tracks expenses for one month at a time
- Tracks various payment modes (cash, check, card) and payment due dates
- Nice summary of expenses by account, payment status, category and week
Excel Personal Expense Tracker by Pedrowave [Option 5]
My comments:
- Simple tracker with easy input sheet
- Option to track by month or by day of month
- A simple chart shows income compared to expenses and savings
Excel Personal Expense Tracker by Romeog [Option 6]
My comments:
- Looks awesome, the expense dashboard is quite versatile with ability to view expense data for any month, any number of days etc.
- Easy to compare categories and choose which categories to include in output
- Simple data entry sheet
Excel Personal Expense Tracker by Tessaes [Option 7]
My comments:
- Clean input sheets, easy to enter the data
- Summaries by category and daily, weekly and monthly statistics
- Simple charts to understand how actual expenses differed from budgets
Please vote for the option you liked most:
Use the comments and tell me which option you liked best. Go!
Thank you
I sincerely thank Bigtaff, Cnat, Ibrahim, Karthik, Pedrowave, Romeog and Tessaes for taking time to participate in this contest and make such beautiful and delightful trackers. Thank you so much for your generosity in sharing what you know.
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 !