concatenate IF to control accounts receivables given Status of it.

[michell feitosa]

I have a spreadsheet to control the accounts receivables Monday to Friday.<o></o>
Iam trying to make it as esiar as possible, to spend less time typing.<o></o>

In the worksheet, there's a IF formula which gives a STATUS of the payment, such as:
#PAID
#TODAY
#TOMORROW
#NEXT WEEK
#OUTSTANDING

The IF formula needs to compare de due date of the receivable with the TODAY() formula on the spreadsheet.

Basic like this:
If TODAY() = due date then STATUS: "TODAY"
If TODAY() > due date then STATUS: "OUTSTANDIND"

It's all good to make the IF formula until the due date falls on the weekend, because if the due date is on;

#Saturday needs to be pay (considered) on Friday, then
#Sunday need to be considered as Monday.

I did a complicated IF formula and I could make Saturdays as Friday, gol.
However, transform a Sunday date to Monday, is the complicate part for me.

Like, if today() is Friday, all the due date on Sundays and Mondays should be
considered on the column STATUS as "TOMORROW". because my work next
day after friday will be monday, so i need that the formula consider the
following sunday and monday as "TOMORROW".<o></o>

ALso you can see in my formula that if today() is Thursday, whatever has a date as
saturday will be considered as Friday then I will have the STATUS
"TOMORROW" whatever is friday and saturday.<o></o>

I copied and pasted below the worksheet, because i could find a botton to attached the file.
Thank you in advance for any help you can provid.<o></o>

Michell Feitosa

This is my formula on column N:
=IF(M5="","",IF(M5>=0,"PAID",IF(IF(WEEKDAY(F5,2)<6,F5,IF(WEEKDAY(F5,2)=6,F5-1,IF(WEEKDAY(F5,2)=7,F5+1,)))<$N$2,"OUTSTANDING",IF(IF(WEEKDAY(F5,2)<6,F5,IF(WEEKDAY(F5,2)=6,F5-1,IF(WEEKDAY(F5,2)=7,F5+1,)))=$N$2,"TODAY",IF(IF(WEEKDAY(F5,2)<6,F5,IF(WEEKDAY(F5,2)=6,F5-1,IF(WEEKDAY(F5,2)=7,F5+1,)))=$N$2+1,"TOMORROW","NEXT WEEK")))))

	Accounts Reciveables										Today's date	15/03/13 Fri
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
WEEK	ID	NAME	$/DAY	$/WEEK	DUE DAY	PERIOD from	PERIOD to	DAYS	AMOUNT	RENT TO BE PAID	MONEY GIVEN	BALANCE	STATUS	OBSERVATION	BANK ACCOUNT
#NUM!	5000	Michell Feitosa	$25.00	$175.00	10/03/13 Sun	10/03/13 Sun	23/03/13 Sat	14	$350.00	$350.00	350	$0.00	PAID
#NUM!	5001	Michell Feitosa	$25.71	$180.00	11/03/13 Mon	11/03/13 Mon	24/03/13 Sun	14	$360.00	$360.00	360	$0.00	PAID
#NUM!	5002	Michell Feitosa	$28.57	$200.00	12/03/13 Tue	12/03/13 Tue	25/03/13 Mon	14	$400.00	$400.00	400	$0.00	PAID
#NUM!	5003	Michell Feitosa	$21.43	$150.00	13/03/13 Wed	13/03/13 Wed	26/03/13 Tue	14	$300.00	$300.00		-$300.00	OUTSTANDING
#NUM!	5004	Michell Feitosa	$25.57	$179.00	14/03/13 Thu	14/03/13 Thu	27/03/13 Wed	14	$358.00	$358.00		-$358.00	OUTSTANDING
#NUM!	5005	Michell Feitosa	$25.71	$180.00	15/03/13 Fri	15/03/13 Fri	28/03/13 Thu	14	$360.00	$360.00		-$360.00	TODAY
#NUM!	5006	Michell Feitosa	$25.86	$181.00	15/03/13 Fri	15/03/13 Fri	28/03/13 Thu	14	$362.00	$362.00		-$362.00	TODAY
#NUM!	5007	Michell Feitosa	$26.00	$182.00	16/03/13 Sat	16/03/13 Sat	29/03/13 Fri	14	$364.00	$364.00		-$364.00	TODAY
#NUM!	5008	Michell Feitosa	$26.14	$183.00	16/03/13 Sat	16/03/13 Sat	29/03/13 Fri	14	$366.00	$366.00		-$366.00	TODAY
#NUM!	5009	Michell Feitosa	$26.29	$184.00	17/03/13 Sun	17/03/13 Sun	30/03/13 Sat	14	$368.00	$368.00		-$368.00	NEXT WEEK
#NUM!	5010	Michell Feitosa	$26.43	$185.00	17/03/13 Sun	17/03/13 Sun	30/03/13 Sat	14	$370.00	$370.00		-$370.00	NEXT WEEK
#NUM!	5011	Michell Feitosa	$26.57	$186.00	17/03/13 Sun	17/03/13 Sun	30/03/13 Sat	14	$372.00	$372.00		-$372.00	NEXT WEEK
#NUM!	5012	Michell Feitosa	$26.71	$187.00	18/03/13 Mon	18/03/13 Mon	31/03/13 Sun	14	$374.00	$374.00		-$374.00	NEXT WEEK
#NUM!	5013	Michell Feitosa	$26.86	$188.00	18/03/13 Mon	18/03/13 Mon	31/03/13 Sun	14	$376.00	$376.00		-$376.00	NEXT WEEK
#NUM!	5014	Michell Feitosa	$27.00	$189.00	18/03/13 Mon	18/03/13 Mon	31/03/13 Sun	14	$378.00	$378.00		-$378.00	NEXT WEEK
#NUM!	5015	Michell Feitosa	$27.14	$190.00	25/03/13 Mon	25/03/13 Mon	7/04/13 Sun	14	$380.00	$380.00		-$380.00	NEXT WEEK
#NUM!	5016	Michell Feitosa	$27.29	$191.00	26/03/13 Tue	26/03/13 Tue	8/04/13 Mon	14	$382.00	$382.00		-$382.00	NEXT WEEK
#NUM!	5017	Michell Feitosa	$27.43	$192.00	27/03/13 Wed	27/03/13 Wed	9/04/13 Tue	14	$384.00	$384.00		-$384.00	NEXT WEEK
#NUM!	5018	Michell Feitosa	$27.57	$193.00	28/03/13 Thu	28/03/13 Thu	10/04/13 Wed	14	$386.00	$386.00		-$386.00	NEXT WEEK
#NUM!	5019	Michell Feitosa	$27.71	$194.00	29/03/13 Fri	29/03/13 Fri	11/04/13 Thu	14	$388.00	$388.00		-$388.00	NEXT WEEK

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup>

 

[Andrew Poulsom]

Check out the WORKDAY function in Excel Help.