Jump to content
We've recently updated our Privacy Statement, available here. ×

How to convert count of days into weeks. 10 days is 1 week 3 days = 1.3 and so on.


Rebecca Roland

Recommended Posts

So you do not want to say that 10 days is 10/7=1.42 weeks, but you want the outcome to appear as a string of:

number of weeks.number of days

is this correct?

In this case, try this. It would produce a string (you can cast into a Real if you want a number):

Concatenate(Integer(Floor([CounfOfDays] / 7)),'.',Integer(Mod([CounfOfDays],7)))

Link to comment
Share on other sites

Hi @Gaia Paolini​ thank you for replying so quickly. I have a dataset with number of days that a member was away, I need to create a parameter to say if away for more than 8 weeks then yellow, less than 5 weeks then orange and so on.

I was hoping to convert the day count to weeks to make that calculation better to facilitate this:

case

  when DateDiff('week',[date_column],Today()) <= 5 then 'Orange'

  when DateDiff('week',[date_column],Today()) <= 8 then 'Yellow'

  when DateDiff('week',[date_column],Today()) > 8 then 'Green'

  when [date_column] is null then 'Red'

end

--------------------------------

User A off for 10 days

User B off for 3 days

that calculation would replace the 'date column' in the colour parameter. Hope I am making sense

Link to comment
Share on other sites

Do you have the number of days the member was away already in a column (the one I called [CountOfDays] in my previous answer)?

In that case you could just express the CASE this way:

 

case
when [CounfOfDays]<=5 then 'Orange'
when [CounfOfDays]<=8 then 'Yellow'
when [CounfOfDays]>8 then 'Green'
when [CounfOfDays] is null then 'Red'
end

 

In this case, you would not really need to calculate the weeks in the way you asked, a simple division by 7 would be good enough.

Link to comment
Share on other sites

Yes, sorry that was expressed in days, but you should be ok by simply expressing the CASE statement like this: where [CountOfDays]/7 is effectively your count of weeks

  1. case
    when [CounfOfDays]/7 <= 5 then 'Orange'
    when [CounfOfDays] /7<= 8 then 'Yellow'
    when [CounfOfDays]/7 > 8 then 'Green'
    when [CounfOfDays] is null then 'Red'
    end
Link to comment
Share on other sites

Yes, I wrapped the calcs, still same error. They are never helpful these errors, thank you for suggesting things.

case

when ([Count of Days Calc]/7) <= 5 then 'Orange'

when ([Count of Days Calc]/7) <= 8 then 'Yellow'

when ([Count of Days Calc]/7) > 8 then 'Green'

when ([Count of Days Calc]) is null then 'Red'

end

Link to comment
Share on other sites

That formula with Concatenate was to reply to your initial question, and it produces a string. In order for it to produce a real number, you need to put Real(..) around it. However, there is no need to calculate the count of days this way, that would be only for display purposes, it is not meaningful as a number. To perform the comparison, you just need the duration in days divided by 7. It will give you a number of weeks as a real number, which can be compared to 5, 8 etc.

So if you have a [duration_days] column, and this is a number, my suggestion is to divide it by 7 to find out the count of weeks.

Then use the case when... etc formula as:

case

when ([duration_days]/7) <= 5 then 'Orange'

when ([duration_days]/7) >5 and ([duration_days]/7) <= 8 then 'Yellow'

when ([[duration_days]/7) > 8 then 'Green'

else 'Red'

end

This will give you 'Orange' when it is less than 5 weeks, 'Yellow' when it is between 5 and 8 weeks, and 'Green' when it is over 8 weeks.  

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...