Forum Discussion
rounding up days formula
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 i...
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"
=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"