rounding up days formula

[tszucsbass]

Hi all,

I am in need of advice, I need to roll up total days to days, weeks to months.
however. every 5 days to be rolled up into a new week and every 3 weeks rolled up into a new month.
also if day value is more than 5 roll up into week but display difference
if week value is more than 3 roll up into month and display difference

the first part i got figured out, but the rounding i have trouble with.

i.e 392days =  12mth 2wk 6day ~ 13mth 0wk 1 day

[#2]

@tszucsbass 

This is a somewhat intriguing question. I note you've had almost 50 views and no replies. I suspect that might be due to the fact that it's not altogether clear what you're actually wanting to do (nor is it clear what the purpose is, which would be helpful to know, since there might be some other way to get from point A to point B)

Are we to understand that you want to take a value of 392 (representing a number of days) and convert that to a single text string (in a singe Excel cell) that reads "13mth 0wk 1 day"? Or could it be three separate cells, with 13, 0, 1 respectively in the three cells?

Now, since that's NOT in fact, what most of us would think 392 days equate to, is it possible for you to tell us what the bigger context is here, in addition to being a bit more descriptive on what the desired output is?

[#3]

If the number of days are in A1, use

=INT(A1/31)+IF(INT(MOD($A$1,31)/7)+IF(MOD(MOD($A$1,31),7)>5,1,0)>2,1,0)&"mth "&IF(INT(MOD($A$1,31)/7)+IF(MOD(MOD($A$1,31),7)>5,1,0)=3,0,INT(MOD($A$1,31)/7)+IF(MOD(MOD($A$1,31),7)>5,1,0))&"wk "&IF(MOD(MOD($A$1,31),7)>5,1,MOD(MOD($A$1,31),7))&"day"