Skip to main content

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

 

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
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)



 

 

 

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

Terms & Conditions