Excel and "Date" usage with formula

[m1ckvb]

I have an excel spreadsheet that I use the "now date" function to set up a cell as to whatever today's date is, hence it changes every day. Then I have another set of cells with dates in them monthly. What I want to do is use an if then else type statement so that then the cells with the typed in date is equal to the "now date" a function will occur adding something together and places the result in another cell. And when the date in the typed cell has passed the function that has occured is left unchanged for that month. I understand how to do the functions but for some reason Excel prevents you from using the "now date" within a formula. Can it be because the now date changes and that in itself prevents Excel (to much for it to handle) from doing what I want to do? Any help would be appreciated.

 

[IRstuff]

Is it not giving you the results you think you should be getting?

I don't see any problem using NOW() in an IF statement, other than you probably didn't truncate NOW() and are trying to compare the floating point date serial number with an integer date serial number.

TTFN

FAQ731-376

 

[Michael2006]

This worked alright for me (Excel 2003):

=IF(A1<=TODAY(),SUM(B11),"")

The date is in A1, the numbers to be added are in B1 to D1 and the result is to be displayed in the cell where the above formula is entered, e.g., E1.

Obviously the second day of the month will be in A2, the third in A3 and so on, the data to be summed in B22, B33 etc., and the corresponding IF formula in E2, E3,...

The "" prevents FALSE from appearing in the cell if the typed in day in column A is still in the future.

 

[ck1999]

My understanding of your question states that you would like to perform a calculation in the row that corresponds with todays Date. And then when tomorrow occurs. The product of todays equation will stay in the cell. If this is correct I would like at putting a macro in the workbook.open location.
The above macro will do similar to what Michael2006 formula does however it will only enter the value in the row for the current date without changing any previous days information

Private Sub Workbook_Open()
Dim lrow
Dim toprow
Dim counter
Dim iValue

toprow = 5 ' this is the row number of your 1st date
tdate = Format(Now(), "mm/dd/yy")
For counter = 1 To 31
If tdate = Range("a" & counter + toprow).Text Then
iValue = Excel.WorksheetFunction.Sum(Range("B" & counter + toprow & ":d" & counter + toprow))
Range("e" & counter + toprow).Formula = iValue
Exit For
End If
Next counter
End Sub


Hope this helps.

ck1999