+ Reply to Thread
Results 1 to 4 of 4

Create list of 53 weeks of the UK tax year and automate their insertion as a heading

  1. #1
    Registered User
    Join Date
    04-01-2020
    Location
    london england
    MS-Off Ver
    office 365
    Posts
    7

    Create list of 53 weeks of the UK tax year and automate their insertion as a heading

    Hello,
    I am a basic user of office 365, and have basic knowledge of creating an excel spreadsheet.

    I am creating an annual accounting spreadsheet, and would like to have each table headed by the week number corresponding to the British tax year which is from the 4th April to the 3rd April of the following year, which means 52 weeks (Sunday to Saturday).

    The two tasks I want to do are as follows:

    Create a list of dates
    (April 4rd 2020 to April 3rd 2020) which look like this:

    Wk 1 –29 March 2020 – 4th April 2020
    Wk 2 – 5th April 2020 – 11th April 2020
    Wk 3 – 12th April 2020 – 18th April 2020
    Continue till Wk 53

    Automation
    My 2nd query relates to automation. I was planning on copying and pasting the dates from this created list, but is there any way I could automate the process? The dates will not be in a continuous list. I will have 53 accounting tables on a long excel sheet. The dates were meant to be the header for each table, so they will at the top of each table.
    Is there some formula I can insert in the cell where I want the dates above to show? If I auomtate it, then I’d like to be able to do it each year for 2021 onwards. Otherwise, I will have the tedious job of copying and pasting each year.

    If anyone can help, I’d appreciate concise easy to understand instructions. Thanks in advance.

  2. #2
    Registered User
    Join Date
    02-23-2020
    Location
    France
    MS-Off Ver
    2019
    Posts
    44

    Re: Create list of 53 weeks of the UK tax year and automate their insertion as a heading

    Here comes a solution.

    Tell me if the date formats are showing fine. I had to change the regional settings on my PC for them to show properly.

    HTH,
    --
    LR
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-01-2020
    Location
    london england
    MS-Off Ver
    office 365
    Posts
    7

    Re: Create list of 53 weeks of the UK tax year and automate their insertion as a heading

    oops just posted a reply which didn't get posted, try again!

    Thanks ArdusPetus, the dates show fine but the year is wrong. It should be 2020. The 52 week UK tax year for this year begins from April 4th 2020 to April 3rd 2021. Then for 2021 it will be April 4th 2021 to April 3rd 2022. and so on, and so on. Accounts books usually include an extra week, so i'd need 53 weeks.



    Can i automate these dates, ie. what should i put in a cell where i want them to show? I don't want to use them as a list, as they will be heading weekly tables. A weekly summary of accounts, with the heading Wk1 - (date), and so on.



    Many thanks.
    Last edited by kaley99; 04-02-2020 at 11:09 AM.

  4. #4
    Registered User
    Join Date
    02-23-2020
    Location
    France
    MS-Off Ver
    2019
    Posts
    44

    Re: Create list of 53 weeks of the UK tax year and automate their insertion as a heading

    You can enter any year you want in cell A2. The calendar will adjust automatically.
    To automate the insertion of dates, use the sample formula shown under the text
    , with Week# as parameter. You ca use the formula with an inline parameter, i.e. for week# 10:
    Please Login or Register  to view this content.
    Cheers,
    --
    AP

    If you have other need, please ask.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] I would like to automate the daily insertion of information into a worksheet
    By krhoover in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-20-2017, 01:38 PM
  2. Replies: 5
    Last Post: 07-26-2016, 09:16 AM
  3. Bar chart for weeks of the year
    By djarcadian in forum Excel General
    Replies: 2
    Last Post: 06-30-2014, 07:55 PM
  4. [SOLVED] Create a list from a Table by selecting a year.
    By JO505 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2013, 08:45 PM
  5. Create a column upon insertion or overwriting of data.
    By Swiatkowski Peter in forum Excel General
    Replies: 1
    Last Post: 12-20-2005, 07:50 AM
  6. Create fiscal Year calendar list weeks by #1-52
    By akid12 in forum Excel General
    Replies: 0
    Last Post: 06-14-2005, 07:05 PM
  7. [SOLVED] Automate Subtotals Insertion?
    By Thief_ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-18-2005, 10:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1