6 Replies Latest reply on Feb 11, 2016 4:06 AM by Shinichiro Murakami

# Replicate excel formula: week calendar - ron de bruin

Hello, I want to replicate a excel formula, and i can´t to replicate:

Excel:

=ENTERO((A2-FECHA(AÑO(A2-DIASEM(A2-1)+4);1;3)+DIASEM(FECHA(AÑO(A2-DIASEM(A2-1)+4);1;3))+5)/7)

My wrong tableau calculation replication (Ron de bruin):

([Fecha]-DATE(DATEPART('year',([Fecha]-DATEPART('weekday',[Fecha]-1)+4)))+

DATEPART('year',[Fecha]-(DATEPART('weekday',[Fecha]-1)+4))+5)/7 Ron de bruin info:

http://www.rondebruin.nl/win/s8/win001.htm

I attach excel calculation and my fail twbx.

Example calendars

http://www.calendario-365.es/calendario-2015.html

http://www.calendario-365.es/calendario-2016.html

thanks

• ###### 1. Re: Replicate excel formula: week calendar - ron de bruin

Hi Carlos,

Are you not just trying to get the current week which can be obtained using Datepart('week',[Your Date])

With this, you can modify as required to suit your calcs.

Steve

• ###### 2. Re: Replicate excel formula: week calendar - ron de bruin

Hi Steve,

Thanks for you response.

Datepart('Week',date) isn´t valid for us, because for example 01/01/2016 is week 53 for us. Thanks!

• ###### 3. Re: Replicate excel formula: week calendar - ron de bruin

So this has got a bit messy for me as Tableau is refusing to accept Monday as the start of the week.

According to Wiki, the calc is Int((([Ordinal Position] - [day of week]) + 10) / 7)

Ordinal position is the day's position in the year so 10th Feb 10/02/2016 = 41

Day of week for 10th Feb assuming the first day of the week is Monday = 3

((41 - 3) + 10) / 7 = 6.857 for which the Int is 6 so the Week for 10th Feb is week 6

Now onto the calcs:

[Ordinal Date] Attr(datepart('dayofyear',[Order Date]))

[Week Day] (nb I had to correct this as Tableau is ignoring the start of the week for me)

If Attr(datepart('weekday',[Order Date])-1) = 0 Then 7

Else Attr(datepart('weekday',[Order Date])-1)

End

[ISO Week]

Int((([Ordinal Date]-[Weekday])+10)/7)

Steve

• ###### 4. Re: Replicate excel formula: week calendar - ron de bruin

Carlos,

I think I've done.

My company also uses workweeks and this is under my coverage

But please double check.  The logic itself can be used, so you can modify if needed.

[Weeknumber]

int(

(datetrunc('week',[Fecha],"Monday")+3    //      12/28 = ww53, 12/29 = ww01

-

makedate(year(datetrunc('week',[Fecha],"Monday")+3),1,1)  //  same year January 1st

)

/7)

+1

Thanks,

Shin

.

1 of 1 people found this helpful
• ###### 5. Re: Replicate excel formula: week calendar - ron de bruin

Thanks SHINICHIRO MURAKAMI! It´s perfect for us. best regards, Carlos

• ###### 6. Re: Replicate excel formula: week calendar - ron de bruin

Sounds good

Shin