ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   If formula (https://www.excelbanter.com/excel-discussion-misc-queries/181126-if-formula.html)

hmsawyer
If formula
 
This formula is not working. Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then I need it
to return as blank. If false (no date in that range), then I want it to
return "Yes".


Thank you.

Mike
If formula
 
Try this.
=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/2008"),'Employee
Attendance'!3:3<DATEVALUE("4/31/2008")),"","Yes")


"hmsawyer" wrote:

This formula is not working. Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then I need it
to return as blank. If false (no date in that range), then I want it to
return "Yes".


Thank you.

hmsawyer
If formula
 
That doesn't work either. I'm still getting #VALUE. Thanks.

"Mike" wrote:

Try this.
=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/2008"),'Employee
Attendance'!3:3<DATEVALUE("4/31/2008")),"","Yes")


"hmsawyer" wrote:

This formula is not working. Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then I need it
to return as blank. If false (no date in that range), then I want it to
return "Yes".


Thank you.

Pete_UK
If formula
 
Try it this way:

=IF(COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

Hope this helps.

Pete

On Mar 24, 10:50*pm, hmsawyer
wrote:
That doesn't work either. *I'm still getting #VALUE. *Thanks.



"Mike" wrote:
Try this.
=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/2008"),'Employee
Attendance'!3:3<DATEVALUE("4/31/2008")),"","Yes")

"hmsawyer" wrote:

This formula is not working. *Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then I need it
to return as blank. *If false (no date in that range), then I want it to
return "Yes".

Thank you.- Hide quoted text -

- Show quoted text -

hmsawyer
If formula
 
That is better, but it is only returning Yes even when there are dates in
April in the line.

Any other thoughts?

"Pete_UK" wrote:

Try it this way:

=IF(COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

Hope this helps.

Pete

On Mar 24, 10:50 pm, hmsawyer
wrote:
That doesn't work either. I'm still getting #VALUE. Thanks.



"Mike" wrote:
Try this.
=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/2008"),'Employee
Attendance'!3:3<DATEVALUE("4/31/2008")),"","Yes")

"hmsawyer" wrote:

This formula is not working. Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then I need it
to return as blank. If false (no date in that range), then I want it to
return "Yes".

Thank you.- Hide quoted text -

- Show quoted text -

Pete_UK
If formula
 
I suppose you are getting "Yes" with dates of 1st April? You need to amend
the first part of the formula so that it is "=", i.e.:

=IF(COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

It would be a bit easier to modify for other months if you change it further
to this:

=IF(COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,5,1))0,"","Yes")

i.e. the first date is the 1st of the month you are interested in, the
second date is the first of the next month, and both COUNTIFs are now
looking at "=" the date.

Hope this helps.

Pete


"hmsawyer" wrote in message
...
That is better, but it is only returning Yes even when there are dates in
April in the line.

Any other thoughts?

"Pete_UK" wrote:

Try it this way:

=IF(COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

Hope this helps.

Pete

On Mar 24, 10:50 pm, hmsawyer
wrote:
That doesn't work either. I'm still getting #VALUE. Thanks.



"Mike" wrote:
Try this.
=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/2008"),'Employee
Attendance'!3:3<DATEVALUE("4/31/2008")),"","Yes")

"hmsawyer" wrote:

This formula is not working. Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then
I need it
to return as blank. If false (no date in that range), then I want
it to
return "Yes".

Thank you.- Hide quoted text -

- Show quoted text -

hmsawyer
If formula
 
That works! Thank you so much!!!!

"Pete_UK" wrote:

I suppose you are getting "Yes" with dates of 1st April? You need to amend
the first part of the formula so that it is "=", i.e.:

=IF(COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

It would be a bit easier to modify for other months if you change it further
to this:

=IF(COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,5,1))0,"","Yes")

i.e. the first date is the 1st of the month you are interested in, the
second date is the first of the next month, and both COUNTIFs are now
looking at "=" the date.

Hope this helps.

Pete


"hmsawyer" wrote in message
...
That is better, but it is only returning Yes even when there are dates in
April in the line.

Any other thoughts?

"Pete_UK" wrote:

Try it this way:

=IF(COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

Hope this helps.

Pete

On Mar 24, 10:50 pm, hmsawyer
wrote:
That doesn't work either. I'm still getting #VALUE. Thanks.



"Mike" wrote:
Try this.
=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/2008"),'Employee
Attendance'!3:3<DATEVALUE("4/31/2008")),"","Yes")

"hmsawyer" wrote:

This formula is not working. Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then
I need it
to return as blank. If false (no date in that range), then I want
it to
return "Yes".

Thank you.- Hide quoted text -

- Show quoted text -

hmsawyer
If formula
 
This works, but now I think it would be better if instead of all the cells
saying "yes" now, only to go to yes after the end of that particular month.

So it would remain blank until 5.1.08, at which time the data would
automatically populate for April. (I changed the blank for a false arguement
to "no".)

Thanks again for all of your help.

"Pete_UK" wrote:

I suppose you are getting "Yes" with dates of 1st April? You need to amend
the first part of the formula so that it is "=", i.e.:

=IF(COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

It would be a bit easier to modify for other months if you change it further
to this:

=IF(COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,5,1))0,"","Yes")

i.e. the first date is the 1st of the month you are interested in, the
second date is the first of the next month, and both COUNTIFs are now
looking at "=" the date.

Hope this helps.

Pete


"hmsawyer" wrote in message
...
That is better, but it is only returning Yes even when there are dates in
April in the line.

Any other thoughts?

"Pete_UK" wrote:

Try it this way:

=IF(COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

Hope this helps.

Pete

On Mar 24, 10:50 pm, hmsawyer
wrote:
That doesn't work either. I'm still getting #VALUE. Thanks.



"Mike" wrote:
Try this.
=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/2008"),'Employee
Attendance'!3:3<DATEVALUE("4/31/2008")),"","Yes")

"hmsawyer" wrote:

This formula is not working. Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then
I need it
to return as blank. If false (no date in that range), then I want
it to
return "Yes".

Thank you.- Hide quoted text -

- Show quoted text -

Pete_UK
If formula
 
You're welcome - thanks for feeding back.

Pete

On Mar 25, 1:14*pm, hmsawyer
wrote:
That works! *Thank you so much!!!!



"Pete_UK" wrote:
I suppose you are getting "Yes" with dates of 1st April? You need to amend
the first part of the formula so that it is "=", i.e.:

=IF(COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

It would be a bit easier to modify for other months if you change it further
to this:

=IF(COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,"="&DATE(2008,5,1))0,"","Yes")

i.e. the first date is the 1st of the month you are interested in, the
second date is the first of the next month, and both COUNTIFs are now
looking at "=" the date.

Hope this helps.

Pete

"hmsawyer" wrote in message
...
That is better, but it is only returning Yes even when there are dates in
April in the line.

Any other thoughts?

"Pete_UK" wrote:

Try it this way:

=IF(COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,1)) -
COUNTIF('Employee Attendance'!3:3,""&DATE(2008,4,30))0,"","Yes")

Hope this helps.

Pete

On Mar 24, 10:50 pm, hmsawyer
wrote:
That doesn't work either. *I'm still getting #VALUE. *Thanks.

"Mike" wrote:
Try this.
=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/2008"),'Employee
Attendance'!3:3<DATEVALUE("4/31/2008")),"","Yes")

"hmsawyer" wrote:

This formula is not working. *Can you please help me correct it?

=IF(AND('Employee Attendance'!3:3DATEVALUE("4/1/08"),'Employee
Attendance'!3:3<DATEVALUE("4/31/08")),"","Yes")

If any cell in the line has a date of 4/1/08-4/31/08 entered, then
I need it
to return as blank. *If false (no date in that range), then I want
it to
return "Yes".

Thank you.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


All times are GMT +1. The time now is 11:43 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com