Skip to main content
Question

Display UK PAYE Month based on a date

  • April 21, 2025
  • 2 replies
  • 51 views
J
Forum|alt.badge.img+4

Hi Everyone

I’m sure that this has been done before but I can’t seem to get anything I try to work 

Im looking to create a forula filoed that looks up and displays the PAYE Month a date in another field falls into for example 17/04/2024 would lookup and display PAYE Month 1

02/10/2024 would display PAYE month 6

20/08/2025 would display PAYE Month 5 

 

I am dealing with the tax year in another field so I dont want to worry about the year 

below is the parameters I have tried if statements I have tried a switch example but cant get anything to work 

 

I have tried setting a year start date and using dateAdd to add on the number days after the year start  but I just not getting anything to work right 

 

If someone could point me in the right direction I would be really gratefull

Cheers

Jackie

From Date   To Date   Days Between No. Days from start of tax year Should Display 
6th april 5th May 29 29 PAYE MONTH 1
6th may 5th June 30 59 PAYE MONTH 2
6th june 5th July 29 88 PAYE MONTH 3
6th july 5th August 30 118 PAYE MONTH 4
6th august 5th September 30 148 PAYE MONTH 5
6th september 5th October 29 177 PAYE MONTH 6
6th october 5th November 30 207 PAYE MONTH 7
6th november 5th December 29 236 PAYE MONTH 8
6th december 5th January 30 266 PAYE MONTH 9
6th january 5th February 30 296 PAYE MONTH 10
6th february 5th March 27 323 PAYE MONTH 11
6th march 5th April 30 353 PAYE MONTH 12

 

    2 replies

    TheTimeSavingCo
    Forum|alt.badge.img+31

    Does this look right?

    •  

    I’ve set it up here for you to check out.  The formula’s kind crazy though:

    "PAYE MONTH " &
    IF(
      AND(MONTH({Date}) = 4, DAY({Date}) < 6),
      12,
      IF(
        AND(
          {Date} >= DATETIME_PARSE("6 April " & 
            IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1)),
          {Date} <= DATETIME_PARSE("5 May " & 
            IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1))
        ), 1,
        IF(
          {Date} <= DATETIME_PARSE("5 June " & 
            IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1)),
          2,
          IF(
            {Date} <= DATETIME_PARSE("5 July " & 
              IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1)),
            3,
            IF(
              {Date} <= DATETIME_PARSE("5 August " & 
                IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1)),
              4,
              IF(
                {Date} <= DATETIME_PARSE("5 September " & 
                  IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1)),
                5,
                IF(
                  {Date} <= DATETIME_PARSE("5 October " & 
                    IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1)),
                  6,
                  IF(
                    {Date} <= DATETIME_PARSE("5 November " & 
                      IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1)),
                    7,
                    IF(
                      {Date} <= DATETIME_PARSE("5 December " & 
                        IF(MONTH({Date}) >= 4, YEAR({Date}), YEAR({Date}) - 1)),
                      8,
                      IF(
                        {Date} <= DATETIME_PARSE("5 January " & 
                          IF(MONTH({Date}) >= 4, YEAR({Date}) + 1, YEAR({Date}))),
                        9,
                        IF(
                          {Date} <= DATETIME_PARSE("5 February " & 
                            IF(MONTH({Date}) >= 4, YEAR({Date}) + 1, YEAR({Date}))),
                          10,
                          IF(
                            {Date} <= DATETIME_PARSE("5 March " & 
                              IF(MONTH({Date}) >= 4, YEAR({Date}) + 1, YEAR({Date}))),
                            11,
                            12
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )



     

     

     

    Adam, Airtable Consultant - thetimesaving.company
    J
    Forum|alt.badge.img+4
    • Jackie88
    • Author
    • New Participant
    • April 21, 2025

    OMG this works perfect cant thank you enough thought I was going crazy couldnt get it to work for me but I can now see clearly were I was going wrong I can utilise this for a load of my stuff cheers for the help I really appricate it :) xx

    TheTimeSavingCo
    Terms & ConditionsAccessibility statement