If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Excel 2003 - Help writing a formula to get time (w, d, h, m)
How about this:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( " "&INT(A1/168)&" weeks "&INT(MOD(A1,168)/24)&" days ", " 0 weeks","")," 1 weeks","1 week")," 0 days","")," 1 days"," 1 day")& SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( TEXT(MOD(A1,24)/24," h"" hours "" m"" minutes"""), " 0 minutes","")," 1 minutes","1 minute")," 0 hours","")," 1 hours","1 hour")) Also fixes a problem with multiples of 10 weeks. GOOD CATCH!!!! Oh, and I forgot... good fix too! -- Rick (MVP - Excel) |
#22
|
|||
|
|||
Excel 2003 - Help writing a formula to get time (w, d, h, m)
Rick Rothstein wrote:
How about this: =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( " "&INT(A1/168)&" weeks "&INT(MOD(A1,168)/24)&" days ", " 0 weeks","")," 1 weeks","1 week")," 0 days","")," 1 days"," 1 day")& SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( TEXT(MOD(A1,24)/24," h"" hours "" m"" minutes"""), " 0 minutes","")," 1 minutes","1 minute")," 0 hours","")," 1 hours","1 hour")) Also fixes a problem with multiples of 10 weeks. GOOD CATCH!!!! Oh, and I forgot... good fix too! Thanks! |
Thread Tools | |
Display Modes | |
|
|