I'm not finding exactly what I'm looking for in searches. I want to convert hours and minutes to days, hours, and minutes. Our payroll system used to display accruals as Days, Hours, Minutes and now displays hours:min. (ex. 131:35 is 131 hours and 35 minutes). I would like to break this down as Days, Hours, Minutes based on a 7 hour day.
131-Rounddown(131-7) = hours
35 = minutes
131-Rounddown(131-7) = hours
35 = minutes
I think I got it, but was hoping there was already a function that would read and convert the format of 131:35. This is what I came up with:
=ROUNDDOWN(LEFT($A1,FIND(":",$A1,1)-1)/7,0)&"D, "&MOD(LEFT($A1,FIND(":",$A1,1)-1),7)&"H, "&RIGHT($A1,2)&"M"
To yield 18D, 5H, 35M
Quote:
=INT(H22*24/7) &" Days, "&INT(MOD(H22,INT(H22*24/7)))&" Hours, and "&MINUTE(H22)&" Minutes"
This works also. I like it better than the one I used because it treats the 131:35 as a number instead of text. Both yielded the same results, but I like yours a little better.
Excel is awesome. I am not formally trained in it. I never took classes. I just look shit up online. I am good with formulas though. I found a site that had this and copied it but threw in the adjustments to the 7 hour day. It's cool how complicated or even simplistic it can get with formulas.
Quote:
In comment 14927625 robbieballs2003 said:
Quote:
=INT(H22*24/7) &" Days, "&INT(MOD(H22,INT(H22*24/7)))&" Hours, and "&MINUTE(H22)&" Minutes"
This works also. I like it better than the one I used because it treats the 131:35 as a number instead of text. Both yielded the same results, but I like yours a little better.
Excel is awesome. I am not formally trained in it. I never took classes. I just look shit up online. I am good with formulas though. I found a site that had this and copied it but threw in the adjustments to the 7 hour day. It's cool how complicated or even simplistic it can get with formulas.
You can use vlookup instead of index match, but index match is considered better and more dynamic. I don't like vlookup because if you add columns you have to change the references and that just makes things not as dynamic as they should be. I only use vlookup now when I know for a fact I won't add another column (it's just an easier formula to write in that situation) or vlookup true, which brings back data from ranges. I don't know if you can get that from index match and sometimes it's extremely useful.
Quote:
As good as excel is there are things that make no sense for it. The one thing I always use is Index/Match. You'd think they'd have an easier way of doing it but they don't. And I like excel better than Google Sheets but, man, Google Sheets has some excellent formulas like filter, sort, and query.
You can use vlookup instead of index match, but index match is considered better and more dynamic. I don't like vlookup because if you add columns you have to change the references and that just makes things not as dynamic as they should be. I only use vlookup now when I know for a fact I won't add another column (it's just an easier formula to write in that situation) or vlookup true, which brings back data from ranges. I don't know if you can get that from index match and sometimes it's extremely useful.
Yeah, I've always had issues with VLookup. I know there is a way around going from right to left but how stupid is that? My problem is when I am doing formulas like that it is SO SLOW. I do a ton of formulas like an insane amount. There has to be an easier way.
Quote:
In comment 14927647 robbieballs2003 said:
Quote:
As good as excel is there are things that make no sense for it. The one thing I always use is Index/Match. You'd think they'd have an easier way of doing it but they don't. And I like excel better than Google Sheets but, man, Google Sheets has some excellent formulas like filter, sort, and query.
You can use vlookup instead of index match, but index match is considered better and more dynamic. I don't like vlookup because if you add columns you have to change the references and that just makes things not as dynamic as they should be. I only use vlookup now when I know for a fact I won't add another column (it's just an easier formula to write in that situation) or vlookup true, which brings back data from ranges. I don't know if you can get that from index match and sometimes it's extremely useful.
Yeah, I've always had issues with VLookup. I know there is a way around going from right to left but how stupid is that? My problem is when I am doing formulas like that it is SO SLOW. I do a ton of formulas like an insane amount. There has to be an easier way.
Is there a way to go the other way for vlookup? I never found one and in the rare instance, I have to go the other way I've used index match since I learned it. Yeah, I used to use vlookup more, I find index match annoying and cumbersome but to me when I learned that vlookup fucks up the formula when you add columns and index match doesn't it was deal breaker.
Have you ever used vlookup(cell,array,column,true)? I find most people don't know its use but in certain circumstances it's extremely useful. In fact I'm building a model right now and I have no idea how I would have even done the basics without it. If you don't know it basically looks at a range rather than one data point. For example, in my model I'm looking for days passed since a particular day and where it falls in bands of 30 days is what month it should be considered, such as the below:
Start Date End Date Month
1 30 1
31 60 2
61 90 3
and so on.
Excel is my jam. I recently created a formula that was like 6 lines long. Although to be fair, I later realized I probably could have streamlined using the min and max functions rather than adding conditions.
Quote:
but I don't use it. I have way too many issues with it. Index match is perfect. It is just slow when I get down with all the thousands of formulas I use.
Excel is my jam. I recently created a formula that was like 6 lines long. Although to be fair, I later realized I probably could have streamlined using the min and max functions rather than adding conditions.
Lol. That's great. I look at it as problem solving as well as a puzzle. To me there is almost always a solution and how you get there is part of the fun. For nerds like us who are very detail oriented this is how we become creative.
Yeah but one reason I hated index match is that I didn't like matching column names with lookup values.
you could also, instead of doing "A1*24/7" have the formula be "A1/"7:00""
Another way to do this formula would be
=FLOOR(A1/"7:00",1)&" days, "&(INT(A1/"1:00")-FLOOR(A1/"1:00",7))& " hours, " & MINUTE(A1) &" minutes"
maybe
time
is catching up with you.
Quote:
=INT(A1*24/7) &" Days, "&INT(MOD(A1*24/7,INT(A1*24/7))*7)&" Hours, and "&MINUTE(A1)&" Minutes"
you could also, instead of doing "A1*24/7" have the formula be "A1/"7:00""
Another way to do this formula would be
=FLOOR(A1/"7:00",1)&" days, "&(INT(A1/"1:00")-FLOOR(A1/"1:00",7))& " hours, " & MINUTE(A1) &" minutes"
you could also, instead of doing "A1*24/7" have the formula be "A1/"7:00""
The days did not calculate properly. It came out to 1 instead of 2.
OR
=INT($D4/"7:00")&" D, "&INT(MOD($D4/"1:00",7))&" H, "&MINUTE($D4)&" M"