How to write a "Text" or date custom format

[FBB]

I'm trying to create a spreadsheet which would automate my weekly reports. My
book would contain 5 sheets (sheet1 being the report, and sheet2 to 5 would
be the weekly data). In some instances, I want to report that a dated event
(action item, expiration, schedule) is occuring on the date referenced on the
appropriate sheet. In other cases, if not data is present, or if the data
doesn't meet certain conditions, I want instead a Text String to be displayed
(i.e N/A, Pending, Past Due, etc...).

Is there a way to do this without using visual basic? Can it be done with
Custom Cell Formatting, and conditional statements?

I really appreciate the help??

Sincerely,

FBB

 

[Steve Smallman]

Hi,

in this example, I have set up Due date in column A, date completed in
column B and the formula in column C

=IF(ISNUMBER(B2),"Complete",IF(A2="","NA",IF(A2<INT(NOW()),"Past
Due",IF(A2=INT(NOW()),"Due","Pending"))))

A relatively complex nested if that you can apply by changing the cell
references.

Breaking it down:
IF(ISNUMBER(B2),"Complete",IF( - If there is a number in the date
completed column (i.e. a date) then the task is complete, display complete.
If not, then
IF(A2="","NA",IF - is there a due date, if not then show NA, if so
then
IF(A2<INT(NOW()),"Past Due",IF - Is the date listed in A2 before today
(Now returns todays date, INT(NOW()) converts it to a day not a day and
time), if in the past, show Past Due, if not
IF(A2=INT(NOW()),"Due","Pending")))) - does A2 refer to today? if so,
then show Due, if not Pending

take a look at the way Excel stores and treats dates, and then have a look
at the logic of an IF function.

All should become as clear as mud!

Steve