Date Help

want2learn

New Member
Joined
Sep 26, 2014
Messages
7
I have a training document with course names across the top in columns A through O and certification titles in columns P through S.
I am trying to find a formula to put the word “Complete” in the cell P2 when cells A through E have dates in them. The same formula would be used for cell Q2, when 5 dates are entered into F through J and so on.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,735
Office Version
  1. 365
Platform
  1. MacOS
you could count to see if the range has any blank cells
=COUNTBLANK(A3:E3)
=COUNTBLANK(A3:E3)=0
If(COUNTBLANK(A3:E3)=0, "complete","NOT")

Or counta()
Book1
ABCDEFGHIJKLMNOP
1
211111complete
311111complete
Sheet1
Cell Formulas
RangeFormula
P2P2=IF(COUNTA(A2:E2)=5,"complete","not")
P3P3=IF(COUNTBLANK(A3:E3)=0, "complete","NOT")
 

want2learn

New Member
Joined
Sep 26, 2014
Messages
7
An additional question. This formula works if the cells are in sequential order A2:E2, what if they are separated. Looking for cells with dates in cells A2, A4, A8, A16?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,484
Office Version
  1. 365
Platform
  1. Windows
I have unmarked your last question as the solution.
When marking a solution, you should:
- only mark a post if your original question was answered to your satisfcation
- only mark the actual post that contains the solution to the original question (do not mark your own post acknowledging that nother post solved your issue, or any other post that does not contain the solution).
 

want2learn

New Member
Joined
Sep 26, 2014
Messages
7
I see I made an error in the question above. I am working in rows, so the cells should have been A2, B2, D2, & F2.

This is the formula I ended using and I also added an "IF" formula to mark it "Complete" in the cell P2.

=COUNTA(A2,B2,D2,F2)=4
=IF(COUNTA(A2,B2,D2,F2)=4,"Complete","")
 
Solution

Forum statistics

Threads
1,185,966
Messages
5,955,046
Members
438,181
Latest member
viv1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top