Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!

Calculated Field - DATE expression

Avatar

Level 2
Hey All, I have been searching for this for a while and can't seem to find the information so I am trying out with a fresh post - I will happily take links to solutions or details. Goal I am trying to turn the Completion Date into a custom format (weeknumber.dayofweek) - it generally looks like this 34.3 (Wednesday in week 34). Challenge I am running into a couple of different problems. The calculated field does not seem to have any way to find weeknumber from a date - so I am manually trying to calculate that (see below), I would love input on a better formula. There seems to be something up with the date format - it works on PCs but not Macs... or it may be the date-format of the individual user - i need to come up with a universal that the DATE expression will take - but i cannot find any examples of what it will actually accept in terms of formats (i find that "1-1-2019" and "1/1/2019" both works, but neither solve the problem). ROUND.UP - does not seem to be a thing, so I am having to "get around" that with a bit of cumbersome coding. The Formula This is what I have running now - quite long, feedback is very welcome! CONCAT(IF((ABS(DATEDIFF(DATE(CONCAT("01-01-",YEAR(Planned Completion Date))),Planned Completion Date))/7)>ROUND(ABS(DATEDIFF(DATE(CONCAT("01-01-",YEAR(Planned Completion Date))),Planned Completion Date))/7),ROUND(ABS(DATEDIFF(DATE(CONCAT("01-01-",YEAR(Planned Completion Date))),Planned Completion Date))/7)+1,ROUND(ABS(DATEDIFF(DATE(CONCAT("01-01-",YEAR(Planned Completion Date))),Planned Completion Date))/7)),".",DAYOFWEEK(Planned Completion Date)-1) Any help is greatly appreciated. Stefan Stokkebæk
0 Replies