Day of Month

[sandy6078]

I have several reports that Divisions are supposed to send by the 10th of each month. I would like to set up a tracking matrix to send to my boss to show whether or not the Division has sent the report on time.

I don't know how to write the formula so that the day is constant (10th).

 

[chergh]

What formula are you trying to write?

 

[ptm0412]

You may try this: =If(Day(report-day)<=10,"on time", "late"); then you can sort or filter based on this column.
Or alternatively, use =day(Report-day)<=10, this formula will return TRUE or FALSE

 

[sandy6078]

Thanks, I will give this a try and let you know how it worked.

 

[sandy6078]

Well that did not work out.

Here is what I am after. I have 9 divisions that have to send 3 reports by the 10th of the month. Some divisions have multiple people send the reports depending on job duties.

If the cell for that report is blank (date received) and today's date is greater than the 10th of the month then "DNR"

If the Date Received cell has a date entered greater than the 10th of the month then "Late"

If the Date Received cell has a date less than the 10th of the month then "On Time"

I tried the following where J5 = 6/10/08

= IF(AND(F10=" ",$J$5<TODAY()),"DNR",IF(F10<=$J$5,"On Time",IF(F10>$J$5,"Late")))

I get the return of "On Time" if cell F10 is blank.

I have tried different variations the formula but am at a loss. Also, I would like to not have to use a cell with the due date (J5).

 

[Brianwarnock]

If you don't want to use a due date then I presume that you want to use the 10th of the month you are in. I would write a function such as

Public Function testdate(ByVal recordeddate)
mthdate = DateSerial(Year(Date), Month(Date), 10)

Select Case recordeddate
Case Is = " "
testdate = "DNR"
Case Is = ""
testdate = "DNR"
Case Is <= mthdate
testdate = "on time"
Case Is > mthdate
testdate = "Late"
Case Else
testdate = "???"
End Select
End Function

Brian

 

[sandy6078]

Thanks Brian, I will try later this afternoon.

Sandy

 

[ptm0412]

I tried the following where J5 = 6/10/08
= IF(AND(F10=" ",$J$5<TODAY()),"DNR",IF(F10<=$J$5,"On Time",IF(F10>$J$5,"Late")))
I get the return of "On Time" if cell F10 is blank.

It returns "On Time" because you have a space in the Formula: F10=" "
You should change to blank: F10="" or use Function IsBlank(F10)

About the Due Date J5, you could replace $J$5 in the formula with date(year(today(),month(today(),10), but it would be too long.

Anyway, use VBA Function is also OK.

 

[Brianwarnock]

He hasn't a space in the formula , he tests for a Blank in the cell, unfortunately he needs to test for an empty cell, a null value, which both ="" and the misnamed IsBlank check, however it is advisable to test for a blank value as well as somebody is sure to use the spacebar, rather than delete, to remove a value.

Brian