If statements and between dates

[tQk.Sushi]

How would I do something like this in a single cell.

If between 9/7/11 and 9/25/11 then...
If between 9/25/11 and 10/11/11 then...

The idea is that if a participant register between 9/7/11 and 9/25/11 they only have a registration fee of $10 but if they register between 9/25/11 and 10/11/11 then they have a registration fee of $20.

Any idea?

 

[pedro-egoli]

Not sure if this is what you are .

Excel Workbook
	A	B	C	D	E	F	G
1	Fees	*	START	FINISH	Player	Registeration date	Fee Payable
2	$10	Early	07-09-11	25-09-11	1	15-09-11	$10
3	$20	Late	26-09-11	10-11-11	2	08-11-11	$20
Sheet1

My format is set up as dd/mm/yy



Pedro

 

[T. Valko]

How would I do something like this in a single cell.

If between 9/7/11 and 9/25/11 then...
If between 9/25/11 and 10/11/11 then...

The idea is that if a participant register between 9/7/11 and 9/25/11 they only have a registration fee of $10 but if they register between 9/25/11 and 10/11/11 then they have a registration fee of $20.

Any idea?

Maybe something like this...

A1 = some date

=IF(A1<=DATE(2011,9,25),10,IF(A1<=DATE(2011,10,11),20,""))

 

[pedro-egoli]

Sorry about my figures which did not appear .

Formula in G2 dragged down is

=IF(F2<D2,A2,A3)

Pedro

 

[tQk.Sushi]

How did you make that mini spreadsheet?

My chart looks something like: Column A is Full Name, B is "Date of Registration", C is "Amount Paid". What I'm trying to do is to make a formula in column C that will display $10 if in Column B the date of registration is between 9/7/11 and 9/25/11 and show $20 if in Column B the date of registration is between 9/25/11 and 10/11/11.

 

[pedro-egoli]

something strange is happening with my posts. I see the full details in preview however, only part appears when submitted.
anyway will try again.

Excel Workbook
	A	B	C	D	E	F
1	mame	registeration	amount to pay	START	FINISH	Rate
2	Bill	15-09-11	$10.00	07-09-11	25-09-11	$10
3	Mary	08-11-11	$20.00	26-09-11	10-11-11	$20
Sheet1

I used Excel jeanie to show spreadsheet
http://www.excel-jeanie-html.de/index.php?f=1

You will note I have added start and finish dates and amounts to sheet and used them in formula. Reason is that you will probably have different dates over time and different rates.

I only used the initial last date in formula as any rego after this would fall into next (higher) period.

In case formula does not show up in above it is

=IF(B2<=E2,F2,F3)

Pedro

 

[tQk.Sushi]

Hmm, is it possible to have those formulas in one cell?

For example, IF condition 1 is met then display $10, IF condition 2 is met then $20, IF condition 3 is met then $30 -all in one cell.

The conditions are the dates whether somebody registered during early, regular or late.

 

[pedro-egoli]

Try this

Excel Workbook
	A	B	C	D	E	F
1	mame	registeration	amount to pay	START	FINISH	Rate
2	Bill	15-09-11	$10.00	07-09-11	25-09-11	$10
3	Mary	08-11-11	$20.00	26-09-11	10-11-11	$20
4	jack	12-11-11	$30.00	11-11-11	31-12-11	$30
Sheet1

Pedro