Results 1 to 9 of 9

Formula

  1. #1
    Join Date
    Jun 2019
    Posts
    2

    Formula

    Dear All,

    Could you please suggest me how to define the following requirement in Excel.

    In Column A I have some value, in column B Start Date, Column C Finish date. In column D I need to get column A value if column C has date format otherwise column D should display 0.

    Thank you in Advance

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,734
    Unless something changed in the newer versions of Excel, I believe that Excel formulas can only check the value of other cells, not the formatting applied to those values.
    It is important to note that in Excel, dates are really just numbers (specifically, the number of dates since 1/0/1900) that have a special date format applied to them.

    Note that VBA for Excel DOES have an IsDate function, that differentiates dates from other numbers.
    See: https://www.techonthenet.com/excel/formulas/isdate.php

    So I think you might need to use Excel VBA if you want to do something like this. You could create you own function (User Defined Function).
    If this is something you are interested in, but need help in doing, please post back.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,734
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    Join Date
    Jun 2019
    Posts
    2
    Thank you JoeM for your reply. Can you tell how to define if any value in column C is written then automatically column A value should appear in column D.

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,734
    The following formula will return the value of cell A1 to cell D1 if there is anything in cell C1. Otherwise, it will return nothing.
    Place this formula in cell D1:
    Code:
    =IF(C1<>"",A1,"")
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  6. #6
    cliff_1954 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    2
    I'm still learning Access. I have need to calculate the flow capacity of fire hydrants. IF tested (very time consuming on large number of hydrants each year) I will enter the Static Pressure field and the Residual Pressure field the Flow pressure is a constant = 20. I am trying to use the first two fields to arrive at the Quantity Available. Below is the NFPA formula. I could really use your assistance. Thanks in advance.

    Sample Flow Test Results • Static Pressure (SP) 70 • Residual Pressure (RP) 40 • Gallons Per Minute (GPM) 1500 Note - NFPA and AWWA Predicted Flow (PF) is always 20 • Step 1 - (SP - PF) = 50 • Step 2 - (SP-RP) = 30 • Step 3 - (50 / 30) = 1.67 • Step 4 - 1.67 ^ 0.54 = 1.32 • Step 5 - GPM x 1.32 Calculated Rated Capacity - Answer 1,976 GPM

  7. #7
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,910
    CliffHydrant-davegri-v01.accdb
    Here's a table and form to show how to capture data and calculate the GPM. Not enough information provided to know how you intend to use...

  8. #8
    cliff_1954 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    2
    Thank you for the assistance. The end purpose was to determine the water flow available from a fire hydrant by entering the static pressure and residual pressure (pressure available for use after the hydrant is opened). I was hoping not to have to manually figure each hydrant into the data base. I'll give it at try. Thanks again.

  9. #9
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,247
    NOTE: This is a modification of davegri's db from Post #7.


    Entering both a SP and a RP will calculate the GPM.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  2. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  3. IIF(AND formula
    By dr4ke in forum Queries
    Replies: 2
    Last Post: 01-15-2013, 12:54 AM
  4. Please help with formula
    By JeanZander in forum Database Design
    Replies: 2
    Last Post: 01-07-2013, 04:39 PM
  5. Formula
    By hschrunk in forum Access
    Replies: 3
    Last Post: 05-18-2010, 01:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums