Nearest Payday

  • Thread starter Thread starter Wercs
  • Start date Start date
W

Wercs

Hi,

Using Office XP, I need to be able to enter a target date, then have
displayed the next payday from that date.

All paydays fall every 4th Friday, with payday 1 on 20th Aug.04.

Can someone please help with a formula?


TIA.
 
Wercs,

Assuming target date is in A1

=IF(DATE(YEAR(A1),MONTH(A1),1+7*4)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6))>A1,
DATE(YEAR(A1),MONTH(A1),1+7*4)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6)),DATE(YE
AR(A1),MONTH(A1)+1,1+7*4)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,8-6)))

The *4 refers the instance in the month (4th), and the -6 refers to the day
of week (Friday)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You can use MATCH for this, as long as you define Payday 0 to give it its
bearings.

Enter 23rd July in A1, 20th August in A2. Select *both* and drag down to
A15. This should increment down to 19th Augst 05.

With target date in C1,

=OFFSET(A1,MATCH(C1,$A$1:$A$15,1),0)

HTH,
Andy
 
OTOH, that's a bit pesky depending on what should happen if target date *is*
a payday.

=IF(ISNA(MATCH(C1,$A$1:$A$15,0)),OFFSET(A1,MATCH(C1,$A$1:$A$15,1),0),OFFSET(
A1,MATCH(C1,$A$1:$A$15,0)-1,0))

, perhaps.

HTH,
Andy
 
try this

Sub fourthfriday()
myd = Range("a23")
msgbox DateSerial(Year(myd), _
Month(myd), 0) + 28 - Weekday(DateSerial _
(Year(myd), Month(myd), 1)) Mod 7
End Sub
and this formula should give the 4th
=a1-DAY(a1)+1-WEEKDAY(a1-DAY(a1)+2)+28
and this formula should give the day (ie 27 for Aug )LAST friday. ONE line
=EOMONTH($A1,0)+IF(WEEKDAY(EOMONTH($A1,0))>=6,6-WEEKDAY(EOMONTH($A1,0)),-(1+
WEEKDAY(EOMONTH($A1,0))))
 
Hi,

With your date in A1:

=MIN(MOD(DATE(YEAR(A1),MONTH(A1)+{0;1},29)-WEEKDAY(DATE(YEAR(A1),
MONTH(A1)+{0;1},2))-A1,66))+A1

I assumed if A1 _IS_ the 4th Friday of the month, result should stay on that
date.
Advise if it's not.

Regards,

Daniel M.
 
Hi,

Using Office XP, I need to be able to enter a target date, then have
displayed the next payday from that date.

All paydays fall every 4th Friday, with payday 1 on 20th Aug.04.

Can someone please help with a formula?


TIA.
Click to expand...


With the target date in A1:

=DATE(2004,8,20)+CEILING(A1-DATE(2004,8,20),28)


--ron
 
Hi,

Using Office XP, I need to be able to enter a target date, then have
displayed the next payday from that date.

All paydays fall every 4th Friday, with payday 1 on 20th Aug.04.

Can someone please help with a formula?


TIA.
Click to expand...

Oh, just an addition to my previous post.

In my previous formula, if the TARGET date was a payday, it would return the
same day. If you want to return the following payday: e.g. if Aug 20 is
entered and you want Sep 17 returned, then use this modification:

=DATE(2004,8,20)+CEILING(A1-DATE(2004,8,20)
+(MOD(A1-DATE(2004,8,20),28)=0),28)


--ron
 
The number of suggestions posted clearly show
the interest aroused whenever it comes to "paydays" <bg>
 
Hi all,

Thanks to everyone who responded. There seems to be many ways to achieve
the same goal.
I'm going with this one from Ron, simply because it's easy to adjust to use
an earlier start date if needed.

Thanks again.
 
Hi all,

Thanks to everyone who responded. There seems to be many ways to achieve
the same goal.
I'm going with this one from Ron, simply because it's easy to adjust to use
an earlier start date if needed.

Thanks again.
Click to expand...

Thanks for the feedback.

I believe that some of the other solutions picked the fourth Friday of each
month; whereas I interpreted your request to mean every fourth Friday (i.e.
every four weeks).


--ron
 
Ron Rosenfeld said:
Thanks for the feedback.

I believe that some of the other solutions picked the fourth Friday of each
month; whereas I interpreted your request to mean every fourth Friday (i.e.
every four weeks).


--ron
Click to expand...

Yes Ron, your assumption was correct.

Regards.
Click to expand...
 
yeah, I fell for that one<g>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 
Am I missing something

Wouldn't your startdate + 28 provide you with each 4th friday?
Click to expand...

I think what you are missing is that he wanted to enter a random date, not
necessarily a pay date, and then compute the following pay date.


--ron
 
... If you were to ask any employee in any month,
what formula should be used to compute the next payday
Likely answer: =NOW() or =TODAY() !! <g>
 
Back
Top