The secret to this is that times are actually just fractions of days. So to get one hour, you just use =1/24. To get minutes, you'd go with =1/24/60.
I've mocked up your sample and attached it here.
Hope it helps!
The secret to this is that times are actually just fractions of days. So to get one hour, you just use =1/24. To get minutes, you'd go with =1/24/60.
I've mocked up your sample and attached it here.
Hope it helps!
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Did you adapt the formula that was in my sample?
In K9 you should enter: =IF(I9>1/24,I9-1/24,I9)
Copy that down the column and you should be sorted.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
No, Sorry, I missed that one. I really do appreciate it man............I have been sweating over this for 3 days. It all works now.
Thanks
Very cool, glad to be of help.![]()
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
I found a glitch in the formula..........if the time does not go over an hour, the - 1 hour column shows the total time for the call, not a zero.
Ah, okay. I built it that way intentionally.
Try this: =IF(I9>1/24,I9-1/24,0)
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Thanks I'll change that.
Now.....how would I lock the formulas so they can't be changed, but others can input their
Data?
Kinda new at this so sorry for all the questions.....
No problem.
The first thing I would do is create a Style for my data entry cells. (I have a writeup here: http://www.excelguru.ca/node/53) This isn't totally necessary, but sure makes it easier to update later.
When you're creating the style, make sure that you go into "Protection" and uncheck the "Locked" box.
After that, just protect your worksheet, and your users won't be able to edit the protected ranges, just the ones you've allowed.
Some notes to go with this:
Hope this helps,
- The protection can be broken very easily by any guru. I usually just use a blank password to protect the worksheets from accidental damage, but this lets users in if they need to fix a bug.
- I always colour my data entry cells. I use a light green and tell my users that "green means go". Every spreadsheet I've built in this company has green data entry cells so that all my users know exactly where they can enter data, and they know never to enter data in a cell that isn't green.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Bookmarks