×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel and "Date" usage with formula

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

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: Eng-Tips.com Forum Policies

RE: Excel and "Date" usage with formula

This worked alright for me (Excel 2003):

=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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources