payday formula

  • Thread starter Thread starter David Turner
  • Start date Start date
D

David Turner

I get paid semi-monthly in the middle and end of the month, but checks come
on the closest weekday, i.e. for November the 14th and 28th. The formula I
use for the second check:

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH
(NOW())+1,0),2)-5))

Is there a similar one I can use for the mid-month check?
 
David,

Is it not as simple as

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH
(NOW())+1,0),2)-5))-14

or assuming A10 holds the later date, just

=A10-14

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob Phillips wrote
Is it not as simple as

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH
(NOW())+1,0),2)-5))-14
Click to expand...

I wish. Works fine for November, in which last weekday is the 28th, but in
December returns 12/17 when I actually want 12/15 (a Monday) :{

To clarify, if the 15th of the month falls on Sat or Sun, check arrives the
Fri before that, the 14th or 13th.
 
Is it not as simple as

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH
(NOW())+1,0),2)-5))-14

or assuming A10 holds the later date, just

=A10-14
Click to expand...

Bob,

For December, 12/31/03 is a Wednesday. A10-14 would be 12/17/03 but "mid
month" might be Monday 12/15/03.

--ron
--ron
 
Sorry about that, forgot to change back the ##/%&## date when I tested it
for February next year.
Delete the message with 02/15/04 date..

=DATE(YEAR(NOW()),MONTH(NOW()),15)-MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW(
)),15),2)-5)

will work
 
I get paid semi-monthly in the middle and end of the month, but checks come
on the closest weekday, i.e. for November the 14th and 28th. The formula I
use for the second check:

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH
(NOW())+1,0),2)-5))

Is there a similar one I can use for the mid-month check?
Click to expand...

David,

I'd suggest something a bit different.

I'd guess that you wouldn't get paid on a holiday either. So assuming that
"mid-month" is always the 15th of the month, then I would suggest these
formulas:

End of this month:


=workday(DATE(YEAR(NOW()),MONTH(NOW())+1,0),-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0),3)>4),holidays)

Mid date of this month:


=workday(DATE(YEAR(NOW()),MONTH(NOW()),15),(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),15))=1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),15))=7),holidays)

----------------
"holidays" is a range where you have listed the holidays for the year.

If mid February will be the 14th, then you'll need to test for it. In the
second expression, in place of the '15', substitute '15-(MONTH(NOW())=2)' so
the formula would be:


=workday(DATE(YEAR(NOW()),MONTH(NOW()),15-(MONTH(NOW())=2)),(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),15-(MONTH(NOW())=2)))=1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),15-(MONTH(NOW())=2)))=7),holidays)


--ron
 
Ron Rosenfeld wrote
I'd guess that you wouldn't get paid on a holiday either. So assuming
that "mid-month" is always the 15th of the month, then I would suggest
these formulas:
Click to expand...

The Workday function requires the Analysis ToolPak. I was trying to avoid
that. I'm going with Peo's solution for now, which indeed does work for
February. Holidays in the middle of the month that might require a manual
adjustment are rare (Good Friday in 2002, e.g.). These dates are copied as
values via macro to another column which is easily edited if necessary.

Appreciate your efforts on my behalf, though.
 
Ron Rosenfeld wrote


The Workday function requires the Analysis ToolPak. I was trying to avoid
that. I'm going with Peo's solution for now, which indeed does work for
February. Holidays in the middle of the month that might require a manual
adjustment are rare (Good Friday in 2002, e.g.). These dates are copied as
values via macro to another column which is easily edited if necessary.

Appreciate your efforts on my behalf, though.
Click to expand...

I'm glad it's working for you. I could not get Peo's solution (I see two in
the thread) to work on my system.

Here are some examples where my results disagree with Peo's:

Peo Ron
Tuesday, January 14, 2003 Wednesday, January 15, 2003
Monday, April 14, 2003 Tuesday, April 15, 2003
Wednesday, May 14, 2003 Thursday, May 15, 2003
Saturday, June 14, 2003 Monday, June 16, 2003
Monday, July 14, 2003 Tuesday, July 15, 2003
Thursday, August 14, 2003 Friday, August 15, 2003
Sunday, September 14, 2003 Monday, September 15, 2003
Tuesday, October 14, 2003 Wednesday, October 15, 2003
Sunday, December 14, 2003 Monday, December 15, 2003
Sunday, February 15, 2004 Friday, February 13, 2004
Saturday, May 15, 2004 Friday, May 14, 2004
Sunday, August 15, 2004 Monday, August 16, 2004
Saturday, January 15, 2005 Friday, January 14, 2005
Tuesday, February 15, 2005 Monday, February 14, 2005
Sunday, May 15, 2005 Monday, May 16, 2005




--ron
 
This should work

=DATE(YEAR(NOW()),MONTH(NOW()),15)-MAX(0,WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW(
)),15),2)-5)
Click to expand...

I couldn't get it to work. I substituted a cell reference for NOW() in both of
our formulas.

Using your formula:

=DATE(YEAR(A7),MONTH(A7),15)-MAX(0,WEEKDAY(DATE(YEAR(A7),MONTH(NOW()),15),2)-5)

vs mine:

=workday(DATE(YEAR(A7),MONTH(A7),15-(MONTH(A7)=2)),(WEEKDAY(DATE(YEAR(A7),MONTH(A7),
15-(MONTH(A7)=2)))=1)-(WEEKDAY(DATE(YEAR(A7),MONTH(A7),15-(MONTH(A7)=2)))=7),holidays)


I get these (selected) different results:

Peo Ron
Tuesday, January 14, 2003 Wednesday, January 15, 2003
Monday, April 14, 2003 Tuesday, April 15, 2003
Wednesday, May 14, 2003 Thursday, May 15, 2003
Saturday, June 14, 2003 Monday, June 16, 2003
Monday, July 14, 2003 Tuesday, July 15, 2003
Thursday, August 14, 2003 Friday, August 15, 2003
Sunday, September 14, 2003 Monday, September 15, 2003
Tuesday, October 14, 2003 Wednesday, October 15, 2003
Sunday, December 14, 2003 Monday, December 15, 2003
Sunday, February 15, 2004 Friday, February 13, 2004
Saturday, May 15, 2004 Friday, May 14, 2004
Sunday, August 15, 2004 Monday, August 16, 2004
Saturday, January 15, 2005 Friday, January 14, 2005
Tuesday, February 15, 2005 Monday, February 14, 2005
Sunday, May 15, 2005 Monday, May 16, 2005

I did choose to use the 15th for mid-month for all months except February,
where I chose to use the 14th. But using your formula on my system, for some
months it gives the 14th and other months the 15th, even when both are
weekdays. I also get some weekend days in there.


--ron
 
Ron Rosenfeld wrote


The Workday function requires the Analysis ToolPak. I was trying to avoid
that. I'm going with Peo's solution for now, which indeed does work for
February. Holidays in the middle of the month that might require a manual
adjustment are rare (Good Friday in 2002, e.g.). These dates are copied as
values via macro to another column which is easily edited if necessary.

Appreciate your efforts on my behalf, though.
Click to expand...

Oh, if you don't want to use the ATP, and if editing the holidays manually is
acceptable, then:

=DATE(YEAR(NOW()),MONTH(NOW()),15-(MONTH(NOW())=2))+
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),15-(MONTH(NOW())=2)))=1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),15-(MONTH(NOW())=2)))=7)


--ron
 
Ron Rosenfeld wrote
=DATE(YEAR(A7),MONTH(A7),15)-MAX(0,WEEKDAY(DATE(YEAR(A7),MONTH(NOW()),1
5),2)-5)
Click to expand...

I get your results vs. Peo's with your formula. You forgot the change the
4th NOW() to A7.
 
Ron Rosenfeld wrote
Oh, if you don't want to use the ATP, and if editing the holidays
manually is acceptable, then:
Click to expand...

Way too much for my brain <g>. Try Peo's formula again with the suggested
correction.
 
Ron Rosenfeld wrote


I get your results vs. Peo's with your formula. You forgot the change the
4th NOW() to A7.
Click to expand...


Thanks for pointing that out.

However, there are still some days that don't match:

Peo Ron
Friday, June 13, 2003 Monday, June 16, 2003
Friday, August 13, 2004 Monday, August 16, 2004
Tuesday, February 15, 2005 Monday, February 14, 2005
Friday, May 13, 2005 Monday, May 16, 2005

Now I would think that if the 15th is on a Sunday, your payday should be on a
Monday, from what you've posted. But, maybe not.


--ron
 
Ron Rosenfeld wrote


Way too much for my brain <g>. Try Peo's formula again with the suggested
correction.
Click to expand...


I did, but still have some disagreements. See my previous post.

What my formula does is

Compute the 15th of the month.
Subtract one from that if the month is Feb (so mid-month is the 14th).
Add one if the 15th (or 14th) is Sunday.
Subtract one if the 15th (or 14th) is Saturday.




--ron
 
Ron,

Try this in A1 and put test dates in A7 to get a clearer picture:

=TEXT(DATE(YEAR(A7),MONTH(A7),15)-MAX(0,WEEKDAY(DATE(YEAR(A7),MONTH
(A7),15),2)-5),"dddd, mmmm d, yyyy")
 
Ron Rosenfeld wrote
I did, but still have some disagreements. See my previous post.

What my formula does is

Compute the 15th of the month.
Subtract one from that if the month is Feb (so mid-month is the 14th).
Add one if the 15th (or 14th) is Sunday.
Subtract one if the 15th (or 14th) is Saturday.
Click to expand...

Interesting, but not the results I want. I never get a check on the 16th
and Feb is never treated any differently than any other month.
 
Ron Rosenfeld wrote
Now I would think that if the 15th is on a Sunday, your payday should
be on a Monday, from what you've posted. But, maybe not.
Click to expand...

Definitely not. Sorry if I wasn't clear. If the 15th falls on a weekend,
checks are always issued on Friday.
 
Ron Rosenfeld wrote


Way too much for my brain <g>. Try Peo's formula again with the suggested
correction.
Click to expand...

In your OP, you wrote "checks come on the closest weekday." And that was how I
responded.

If you really meant the preceding weekday, then Peo's formula would be
appropriate.


--ron
 
Back
Top