Excel and "Date" usage with formula
Excel and "Date" usage with formula
(OP)
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.





RE: Excel and "Date" usage with formula
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: Eng-Tips.com Forum Policies
RE: Excel and "Date" usage with formula
=IF(A1<=TODAY(),SUM(B1:D1),"")
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 B2:D2, B3:D3 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.
RE: Excel and "Date" usage with formula
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