http://i154.photobucket.com/albums/s.../worksheet.jpg
Cell E73 has this formula: =SUMIF(B13:B72,"Principal",E13:E72) and the same applies for the cells up to E79.
I am trying to find a formula that will automatically calculate the figures from E73-E79 into F73-F79 and show the appropriate number of days.
E.g: if there are 10 hours accumulated for the principal (cell E73), I would like cell F73 to read "1 day and 3 hours".
1 day = 7.5 hours.
I need to finish this by 12pm today (my boss is on my case about finding this formula) and any help would be greatly appreciated!
*I am using 2003
Wouldn't that be 1 day and 2.5 hours? Try this in F73
=INT(E73/7.5)&" day(s) and "&MOD(E73,7.5)&" hours"
Audere est facere
You are a star! Thank you ssssoooo much! It works!
And yes, you are correct: that would work out to be 1 day and 2.5 hours!
Would you mind explaining in detail the formula for my own future reference?
*I seriously cannot thank you enough!
One thing
You have posted an excel thread in word forum.
Any way
=INT(E73/7.5)&" day(s) and "&MOD(E73,7.5)&" hours"
The INT function calculates the integer part of the "E73/7.5" i.e if it is 10/7.5 it will give
1. Integer part of (1.333).
"day(s)" and "hours" are inserted as text with the help of "&".
The mod function return the remainder of the 10/7.5 i.e hours remained.
Regards
Mahju
Mark the thread as solved if you are satisfied with the answer
Rule 9
Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.
D'oh! Thanks mahju, I didn't spot that - moved to Excel generalOriginally Posted by mahju
Audere est facere
This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.
Thread Closed.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Bookmarks