If Function and Dates

[Laura Logan]

I am trying to create a function in a spreadsheet but not sure how to go
about it. I have a start date in A1 and and end date in B1. If column c1
falls between those dates, I want to have the cell in D1 enter the value of
1. If c2 does not fall between those dates, then I want it to enter the
value of 0. Can this be done?

 

[Ron Coderre]

With
A1: (a start date)
B1: (an end date)
C1: (a date)

This formula returns 1 if C1 is between A1 and B1, inclusive:
D1: =--(MEDIAN(A1:C1)=C1)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Laura Logan]

Sorry, question should read:

I am trying to create a function in a spreadsheet but not sure how to go
about it. I have a start date in A1 and and end date in B1. If column c1
falls between those dates, I want to have the cell in D1 enter the value of
1. If c1 does not fall between those dates, then I want it to enter the
value of 0. Can this be done?

 

[Laura Logan]

Thanks Ron. That did the trick.
--
Laura

With
A1: (a start date)
B1: (an end date)
C1: (a date)

This formula returns 1 if C1 is between A1 and B1, inclusive:
D1: =--(MEDIAN(A1:C1)=C1)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Tyro]

Perhaps this is easier to understand rather than that obfuscated formula.

=IF(AND(C1>=A1,C1<=B1),1,0)

Tyro

 

[Pete_UK]

How about this:

=AND(C1>=A1,C1<=B1)*1

Obfuscated enough ?

Pete

 

[Tyro]

Apply the KISS principle, just keep it simple

Tyro

 

[Tyro]

You have really no understanding of simple things. The more obfuscated it
is, the better for the learner.
I can give you obfuscated things that would blow your socks off. But to
whose benefit?

Tyro

 

[Pete_UK]

Well, the OP here thanked Ron for his formula, so presumably she
understood it in the end. Sometimes it is better to stretch knowledge
in order for the learner to gain a better understanding of the
solution.

Pete

 

[Tyro]

I disagree entirely. The OP needs to learn the simple things, complicated
(obfuscation) will come with time.
After 44 years of computer programming, I truly believe in keeping it
simple.
I can obfuscate to the point that most programmers will say what is this
that works?

Tyro

Well, the OP here thanked Ron for his formula, so presumably she
understood it in the end. Sometimes it is better to stretch knowledge
in order for the learner to gain a better understanding of the
solution.

Pete

 

[Sandy Mann]

After 44 years of computer programming,

Your apparent need to keep telling us how long you have been in computing
makes you sound very under confident. I've been driving for 49 years - but
it doesn't make me Lewis Hamilton.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Coderre]

Hey! How'bout them Red Sox, eh?

Respectfully,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

After 44 years of computer programming,

Your apparent need to keep telling us how long you have been in computing
makes you sound very under confident. I've been driving for 49 years -
but
it doesn't make me Lewis Hamilton.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

You're talking to Limey here Ron - I'm afraid that that goes right over my
head.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

Unless he means Liverpool last night in the San Siro !! <bg>

Pete