# Formula

1. Novice
Windows 10 Access 2016
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.

2. 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.

3. VIP
Windows 7 32bit Access 2007
Join Date
Jun 2012
Posts
3,734

4. Novice
Windows 10 Access 2016
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. 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,"")`

6. 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. 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. 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. Master of Nothing
Windows 7 32bit Access 2010 32bit
Join Date
Sep 2010
Location
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.

#### 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