Week = ToNumber(DateTimeFormat([Date],'%U'))
Year = ToNumber(DateTimeFormat([Dat...
Input21
2016-12-31
2017-01-01
2017-12-31
2018-01-01
2018-01-07
2018-12-31
2019-01-01
2019-01-06
Text Input (16)
Output24
Horizontal
MS_Date_to_Week
This macro calculates Year, Month, Week and Start of Week fields from Date field.
'Start of Week' field can be used to group data based on week.
Start of Week is Sunday.
Preparation
date to year, month, week, start of week
Marya Sangeetha @maryas
Tab
Questions
Tab (20)
MacroInput
Macro Input (21)
Macro Input (21)
MacroOutput
Macro Output (24)
Macro Output (24)
Macro
iVBORw0KGgoAAAANSUhEUgAAAKsAAACrCAYAAAAZ6GwZAAAABGdBTUEAALGPC/xhBQAAAAlwSFlzAAAOwAAADsABataJCQAAEyJJREFUeF7tnYmXHEUdx/2/PDiTGGJMlASC4UrkKSCICgJySAwSwQN9D5+ivKeAohEPTpUHyLXZe2dnr9n7vu/dmb0nm5S/b28NO9v7m9mu7p7Zqd7f973vy+ykqqe76tPVdfdnlEhkiSID6/rlyyqZvqhm19IqfemS/nbv6RKlQ+riRjqsRSwdIgHrRcqg8ulZ9bOOHvVka6d6c2RczVBm7TUhHSooHX7V2avOJqKXDtbDilL0/NCIuqk6rvaX1ah9n1SrL5fXqcdaOtTQ8ooOFX0tXVxXfx3cSIcvZtKhoo5u3i41uhKNdLAa1vSly+rfoxPq6rJqdQ1lzrXa+IzMQimbpEdi1LVKN+zfBkfVoQu129LhAIH7DD1xUOraLqth7VlcUnfEmj7NHLdRwr5Oj8J0BDIqn5qTKadE5dIAPkzpUDY1q2xPBath/Q+VqihNuAzK+HRtk+pbWtYxoic0LM+1dW0pUd2+8uMqdYbqsMvr6zqWnbIa1pf6htjMyfYVlFFvUOka1bJ1dGVVHaG6aT5Y4btjzU4Pgc2yGtbf9QywGZPtq8nPdvRGtjurZnqOvW63b6uJq2HLG5zWworH30/bu51HHJc52X68pcP6R2AuvTE8pj7vIQ1OVMVVL9XxbZbVsP6QIPQC6/caW9XCxWjC+pf+YU+wfonq9m3JBR3LTlkN6wMEoRdYvyOwqusI1sR8SseyU9bCir7Fu+ubPcH6UHO7WopoNeAfQ6OeYN1PsMbn5nUsO2UtrCsE652xJk+wPhzhOiu677zCWjszp2N5E55emGuAAYVs71bPSsFhxYVhpGkunVZdC4uqkR5Fn0zNqnfHJ9Xr1Dj4F5UMGePvt0fH1f8mptQHk9NORzZcOzOvxlZWNw6otULwfSPWrK7ykFFPJDoduN1Cwsdnk6reEjdTndMNyocT055hrZzmBwaQN6jPVk7POWmP0vqPfYPqua4+pw/3bGuneripzfn3GWrUvtA74ORVFYVHni5SFQtgF1oFhRWlWWtqwbl4TKw4VduojlXGduzI34exbfJ12hgyfGdsQmXjhsf6LTW5R22yjQkuXNcVzg/nc6S8Tn21IlbSxmgcniTuJwQA5K6ZcwXd+G4hVVA631BVrw5TviDdUQDgBshnDGcfpXNCnj5L6YvCBwUKSuJCqSCw4nTRWY078E6qVx4ur1VX0cV5eWRzRuK9PTLuG9bnunrZOx8ZjxKHi1NqRn/xrXS9blgbqB7Khef8MT2t3KmANH11cMTTE4oz8hTg3lBZrx6htkH59EzBpiaGDiugiM3OU+OnxZkFxV2gH783NlWQkhWtZC5OKZqDtSWZZMNy/iQPrFx4Pz5G0P5pYLggE4hChzU+n1Q355lU4dd4zGTLBNZn2nvYux312J2qJKXkoLB+PFV4WDN+oWeA2hXb0zyIQoMVJSoaALdTHcbvIyWfg8D6eAvfwMJ3x6kk4OKUmvG4vYueVu7rqKEWPheec9nkTNFgPXihRp2n44YJbGiwolX47XjCmTjCnXxQu2FFpqFyj7ocFz7bD+boZ7UNVvQru2H9aGLaU+GAhhNWEbhhxd//opY9FyeIkS+Y/P0OVd/CqsOGAitK1V929joJwp14GOZg/WbM26DAtxsSzrokt6IAKxqeaJ1zcbKdr5/1rdFxNk5Q4yZC92J7Kpxh3lBgRSIcKq9jTzgsu2FFH6nXQQH0SMynt0+PiwKsr494m8gCWNHw5VQoWOH9ZdXq6bZu/UvBFBjWRSqxvknQfMFDggWxG1aU5g80eZsbEAVYUUrdRaWUG9bXPMJ6gGBtZuYG4GiFqLNmG2vC0KV1yV0HMVQgWPHbGEFBpz13kmEasGZfKzqfH21pD1YNoHrs0YoYG6cUzfUGeIUVHfidzOO4GLBuPBVa1GDAFRuBYMVkEiz7Racwd5JhGqMsW8sU5TxevMD6A2pguTMZwne2DArAHKzvj095amBhPms/A0sxYIUPXKhxVt9i6N2vAsE6vrLqVKC5kwvTqGJgDbx7FAp9eTst50Cp8zyFQx3Xrbm1NBunVA1Ycc7ZaqJHOxfW7VO1DdvmV0BI05f7d14eFIZP1TU6zPhVIFgxweRoRWEbVvDnPqpUL1KCuoH788Cwpx6IuhwNCyzzwLG5OKVo1K/dS1PQJXfMQ1Xmew2tbL0dI3u/6e5n44RtPAVfonz0q0Cwvjo4qq4tQn0VJSuWsLj769BveH1l7oxC9QSrCebS/NBf41zSKlgxNNw0n9Rnv6lfdPTkfcKgmgAguSFnzJh6qrWbjVcIn6iKqQWfQ7G+YUUZh+lihRoEcBuNJOxlla3ptTV1trUrZ731xqp6VTUzt60jPKPXhsesghVPkXfGtjY0oZZkSp2saWDjIG2+XtvI1lehCXos30mNHy5uIYzlNROra/rXzeQbVtR1vkuPlmLBeozAcyc4egSwCA7LW9zVAQz3oZqQa/QEXUDYWsdL46SUjBLS3X2Fhu7fh8acng339dxGddWyqe3DrBm1JhfUV/I8ncL2rsCKBDpNCbFTAycsY8LJh5PT+tc3hUzAdEQ8CjEw8VkqKTH38499Q3kfN6j73VaXezeXUjX6WsdXtzdSMAaPYVMMcSINUGV4vLndWXflbphm643RcQpb+KpcxrsG6+05Hj2FMMaaf0LVjlzJjgWBlVOz6hWqwGMGu7v0cQtdPph0zf1WKRsTsTHhmhOeNBhN/BOlwQcTUzvuIIi0fILq9NzvFMq7AitgOFldvNEflOBoDXemFnMCC6EUyff/EFrQZxIdRekfDts453Nt3WxXHIRvkQb5b9WNcKjrYmI89zuF8q7Ais7pYsKa8aP0aJv0ebEQMvLtsY09sopVhQnbeMSjdM33eN9JE1SVuC/eUvQ6+56CFQ2nl/tzN5zyCVnbOJ90egm4Y9ti3GSnqYWPRX5+cEUVDv2dxRgmd3tPwQqjrvnexPbG1k4aW1lxOse5Y9ro++laUEKaCjO1dqu+vudgdR5dVCq80DvodF2tUkuYW1WJbzAWjf7Yd6nBcaq2ybquqnzGtaCExXJ1LKfGtXIlLdIG9XSkFZZXc8cqlvccrBlj6tl98YT668CIs7yje2HR2Zod3VL9lDEY7fnv2KR6qq3L6dIpVp9wMY1rwiSVp9u7nWvFNEDMbkIaIC2QJkibP/QNOWnFHaOY3rOwZvxlatHeWtOg7qIGAwYI4HsoY75e1+T0IKAFbWtjyqv3k3Gtd9A1o6qTSQekCdIGkHDxiu09D2u2AaaNXVJhOpMGpZgOAqvYGgusYmsssIqtscAqtsYCq9gaC6xiayywiq3xrsCKJSYnLJ8QIi6+dwVWvK0OOyVzJyQW5/KuwYof5k5ILM5lgVVsjQVWsTUWWMXWWGAVW2OBVWyNBVaxNRZYxda45GHF9jRPJjrV89394gj6DOUt1sNxee92ycN6qBxbNaacXUTE0XPVzKw66JGFkof1SEVMtacWdUxR1JRIppzVw1zeu13ysGK7yoGALz8Qla7wwj6v22aWPKwnquNqdHnr9uKi6AgF0XGPM/BKHtabahroBP2/+EBU2sKGGtibgMt7t0se1q/RhUwKrJHV2PKq84JpLu/dLnlYb65tUDNr/k5QVPoSWEXWSGAVWSOBVWSNBFYS9hrFC9fC8NL69ne34vjzTFg/xsvP3MK+qPieC29qvCjZ/dpT/InvufCmxgJQv9u+C6wkJODPO3rVY4nOwH5tZFwfdVPYrvzJtm42vKn/MjjiDD1mC6D+vmeADW/q53r6t70qCS8hwWtCufCmPtfR4/u9qgIrCW+/w3JvvFYoiLHh7rn2bn3UTWHvg310DVwcUz/e0rHtPQe42e6oa2TDm/pUXZOzg3e2UKo+1NTKhjf1kcqY6llY0kc2k8BKAqw3hbA3AbYzzwVrWK9wxztic8HKhTf16RgP68NNbWx4U2O4VGAlC6x8HBNjF293GgqsHiWwbrWUrAKrwKotsAqsAqu2wKotsPJxTCywBoQV7xDlTshtgZWPY2KBNSCsXjNaYOXjmFhgFVgFVm2BVVtg5eOYWGAVWAVWbYFVW2Dl45hYYBVYBVZtgVVbYOXjmFhgFVgFVm2BVVtg5eOYWGC1ANYbCdZrmGOaeLdhxe9zcUyMKYK5YL0yhOMLrNpBYD1RXa/2ldU4v+XX11L8p3PAiiFjLo6pf5jozAkrbjYujonzzmcNmD7wiaq46lsUWH3DijVN/UvLzh0f1CM59triwvoxtz0SFgwO0O9y4U2N/aTSrhWDuDVG6Ibmwpt6kI7vXkPmVQKryBoJrCJrJLCKrJHAKrJGAisJ1X202LGbSlBjQwtOSxf58Kbmjh/m+S+vX3KOly38vUrfc+FNjfNEg9CPBFbSfDqtftnZq37U2qnOBjDi/3N4TB91U2kC7MetXWwcU58fHNm2/Q4gcN5mwoQ39fM9A05XVbawI8srA8NseFM/29Eb7R1ZCg1rVAYFvlDgQQEuvKmPVsZU94L0swaCNQrDrUFvNliGWwXWosDKhTe1wBoQ1gMC66cuNKyFrgZEHlb8MHdCbgusfBwTC6wCq1QDtAVWbYGVj2NigVVgFVi1BVZtgZWPY2KBVWAVWLUFVm2BlY9jYoFVYBVYtQVWbYGVj2NigVVgFVi1BVZtgZWPY2KBVWAVWLUFVm2BlY9jYoHVAlgx+RqwBTEmP+eCFRtocHFM/VgeWLnwps4F64NNrWx4Uwus2kFgva8hoU5Wx7UbXJ/dzvV9g/pNd78+6qYA6x11Ta6w2cfgPnO/EXeW36y5NqEArI80t20Jt/VfzlyYuHMzzKXT+sgbwouMn6GbMHfcrcfgP2/8fW884Wx04UcCKwlrpNpSCyo+N++4fi7p+rzhzc+b37v/xc4ubmGBXIMr3NZj47cy/7fxeet3m+F6F5e2LejDDirdC4tO2J2Osfl5+zXgu47U4rYdWTI71mSOsRk38ztbj7H5t/v351XLfMq5ef1IYBVZI4FVZI0EVpE1Eli1VqgeFYZzbXLBhfXjYhyf24IC33Ph/TjSm1wUGtYpuqhHmtvV3fUtgf1i35A+6qaQQfc3JtjwJv4W+Xc9A5TZ+sBa6Fp6MtHJxjH1mUSHs+lHthYurqvnuvrY8KZ+tLkj57agO0lgJaHr6nhVTF3NHNPE6EfM1c+KjXjx/1w8E+cbFLgihOPnGxT4fAjHl35W7SCwygjWhmUES2AVWLUFVm2BlY9jYoFVYLUG1rwvwGDCm1pg1RZY+TgmlpJVYBVYtQVWbYGVj2NigVVgFVi1BVZtgZWPY2KBNQCsyIgj5XXsCbktsPJxTCywBoAVGX1DpTeQBFY+jokF1oCwfs0jSAIrH8fEAmtAWE9WC6wZC6wCq8CqLbAKrAKrtsCqLbDycUwssAqsAqu2wKrtF9ZJuqjbKO6+CzWBfa6tSx91U7iGI5RJXHgT7yfj/bDudf2z6bS6N97MxjH1N+g4M2tbl7XgZsAmGlx4U5+saXD2PvAjgZWENVKV07Pqo8npwG5LLeqjbgoL5MqnZtjwJv6Y3DyfYja5uKRqZ+fZOKauo+O4FyVik4sm+l0uvKkrZ2adHV78SGAVWSOBVWSNIgXrTVQfmlj19y57UelreHlF3Up5zOW92xbAGldj1LIXRVMDS8vEQpzNe7dLHtbrq+pVn8+Wpqj01bWw6HR9cXnvdsnDeqi8llqtSR1TFDXFZuedPOby3u1dgRXdKLd7rKccLKtRH0xM65iiqOmt0Qm1n/KYy3u3dwXW9cuX1X3xhOetcX7V2ctuLCayW8jTs61dnrd42hVY0T390/YeT7DiQm6mUrg1mfK9U52o9IRBCQyGHK3wtmIE3hVYodeGx9kT4owXTdxT36LKpmacvfExuiPg2ifkGfJuei2tPqSq3Z2xZmfuBZfnbqPQQhfXIrV3/CgQrNiP3+SugtEzgEkf5wdH1PvjU+oCwVszPefJGHJsmBOHaS6dcxl59e74pJN3j7W0qxur4mofk8e5fA35xf4h39XBQLCOr6yqu+jO4k5sJ2e2acSsJxMfFodqLo05HyQfIGfnnamPU0E15HMPWCgQrJgS99uefqO7S7w3DUZ+3d23bRqliQLBiuIcr6dBpZk7QbE4Y9RV2xcWA/UIBYIVWlm/5Lw47EqfjwZx9P1Faly/OTJOpWqwBnVgWCHs/Y+uqaDbqYujaTSo0YMQVKHACn04OaOOV9Y7LT7uhMV707fXNTlvRwxDocGKWebnB0epxVgjwIodBk5Ux9W741OBGlXZCg1WCK+6eaF3UF0nDa497yMVdert0QmnTROWQoUVworLvw2NqkMC7J71MaoOokG1ElKJmlHosEIo9rEI7RZpdO05n6ptVJ9Q3hdiKL0gsGaE14N/v7FVHSiTemyUjbzFFMF74y1OnhdKBYUVd9fYyor688CwOk133OHyOnEEjbx9qX9YDS0tF3QaaEFhzQjVAkx6eZXqsuLouSWZCrUhlUtFgRXamFomjqILWZpmq2iwikRBJbCKrJHAKrJGAqvIEin1fy63ojygDFXLAAAAAElFTkSuQmCC