Forum Discussion

Copper Contributor

Jul 31, 2024

split or create a funtion

hello everyone, i have a problem to put automaticaly range of daily works, and that is slowly to me So that I want to create a function or have help how to do it. like in semaine 53 = I want to th...

Formulas and Functions

Diamond Contributor

Jul 31, 2024

This way it doesn't work

Platinum Contributor

Jul 31, 2024

It's very strange, it works for me. I'm currently using Excel 2016.

HansVogelaar
MVP
Jul 31, 2024
NikolinoDE

Hi Niko, your formula probably depends on your system date settings. It returns #VALUE! on my computer too.
- m_tarler
  Bronze Contributor
  Jul 31, 2024
  NikolinoDE that is a clever way to do it. Maybe use the ISO standard for the date format then it may be more universal (year-month-day):
  =(B2&-1&-1)+A2*7-MOD(B2&-2&-1,7)-4
  HansVogelaar and SergeiBaklan can you confirm if this version works for you too?
  - SergeiBaklan
    Diamond Contributor
    Jul 31, 2024
    m_tarler
    
    Yes, ISO always works
    
    Another comment, that doesn't answer on initial question. Based on screenshot week numbers are not for calendar year (for financial year ?) . In this year week #52 is the last week in August, week #53 is first week in Sep. I'm not sure how first week starts - with any day in the first month, or with only any work day in the first month, or something else.
- NikolinoDE
  Platinum Contributor
  Jul 31, 2024
  HansVogelaar
  That's right, now I've checked and the German formula =(1&-B3)+A3*7-REST(2&-1&-B3;7)-4 cannot be automatically translated into English by Excel, it doesn't accept.
  The formula may be trying to perform arithmetic on concatenated strings, which could be problematic when (not sure, but could be most likely). Here is a revised approach in English, it should actually work, even i dont test it =VALUE(1 & -B2) + A2 * 7 - MOD(VALUE(2 & -1 & -B2), 7) - 4
  
  Thanks both of you for the tip

Resources