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

[Rebecca Roland]

Examples

3 days = 0.3 weeks

10 days = 1.3 weeks

7 days = 1 week

5 days = 0.5 week

14 days = 2 weeks

[Gaia Paolini]

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)))

[Rebecca Roland]

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

[Gaia Paolini]

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.

[Rebecca Roland]

@Gaia Paolini​ yes, let me do that and try.

[Rebecca Roland]

Oh but the 5, 8 and 8 is number of weeks so I would need to change them to days. So 5 weeks is 35 days

1. case
2. when [CounfOfDays] <= 35 then 'Orange'
3. when [CounfOfDays] <= 56 then 'Yellow'
4. when [CounfOfDays] > 56 then 'Green'
5. when [CounfOfDays] is null then 'Red'
6. end

[Gaia Paolini]

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

[Rebecca Roland]

It returns an error :-/

It says 'invalid type for function call '/''

[Gaia Paolini]

have you tried surrounding it with ()?

([CounfOfDays] / 7)

[Gaia Paolini]

also you might need to check the order of the when clauses, or make them more explicit e.g.

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

[Rebecca Roland]

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

[Gaia Paolini]

can you check what the data type of CountOfDays is?

[Rebecca Roland]

Morning, sorry I had AL. Back today!

This is the CountOfDays calc Concatenate(Integer(Floor([duration_days] / 7)),'.',Integer(Mod([duration_days],7)))

[Gaia Paolini]

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.