Do Calculation then place the Value in a field

[gheyman]

I have a field in a table that should show me the number of months between two date. Is is possible to due the calculation between the two dates and store that value in the field?

Does this have to be a two step process? Do I create another field to do the calculation (one that has a formula in it) and then take that answer and put it as a value in my first field?

Or is there a way in VBA to do this all in one Step?

 

[PhilS2520]

Hey G Heyman,

When you say “table” do you mean an Excel table, a special construct, or a worksheet, a document in Excel?

When you say “field” do you mean a field in an Excel table, or a cell in a worksheet?

You can store a value in a cell in a worksheet if that value is a) a number, b) a string (text), c) a formula, or an error, which in and of itself is not a type, but I just wanted to put it out for completeness.

Excel manages time and date by using a date serial number that represents the number of days since January 1, 1900, or 1904 in some cases. So a number like 43210 will be April 22nd 2018. To figure out the date serial numbers between two dates simply subtract the smallest from the larges and to find out how many months, we’ll divide by 30 as an approximation.

I hope I answered your question

 

[iliace]

Let's say you have a date in A2; as an example, let's use 1/15/2018.

In B2, you have the number of months; as an example, let's use 3.

In C2, you can use a formula like this:

C2 =DATE(YEAR(A2), MONTH(A2)+B2, DAY(A2))

The result of the formula in C2 will be 4/15/2018, which is 3 months after the date in A2.

If you want to hard-code this new value in C2, you can copy/paste values (using Paste Special). To be honest, I have a hard time thinking of a circumstance where this would be necessary, unless you have a very large sheet and need to minimize calculation time. If A2 and B2 are subject to change, then a formula-based calculation in C2 is usually the way to go.

And yes, you can program all this in VBA if necessary.

 

[Joe4]

Phil & Iliace,
They posted this in the "Microsoft Access" forum and not the "Excel Questions" forum, so they are probably looking for an Access solution rather than an Excel one.

gheyman,
I am a little confused by this line:

I have a field in a table that should show me the number of months between two date. Is is possible to due the calculation between the two dates and store that value in the field?

Can you walk us through a simple example of what you are trying to do?

In Access, data normalization rules say that should not store any value which can easily be calculated at any time from other fields. Doing so can makes it not dynamic and can undermine the data integrity of the database. Calculated fields are usually done in queries (newer version of Access allow for limited calculations at the Table level, but it is really not advised to do the calculations there).

 

[gheyman]

Let me try.

I have an Anticipated award date of a contract (Example: January 01, 2018). Then I have a date that a certain Task (Task1) should happen after that award date (Example: March 31, 2018). So the formula should calculate the number of months between these two date (3)

I want it stored this as a value for a reason. I want to be able to have a function where the user can change the Anticipated Award date and have the Task1 date automatically update. So if the Anticipated start date slips to February the user can click a button (or some other action) and the Task1 will update to 3 months later (April).

Now I can "inefficiently" work through this by having a two fields (other than the date fields). First I have a filed (Field3) that calculates the number of months. Then I take the value from the calculated field and store it in another field (Field4) as a value.

Anticipated Award Date (Field1)
Task One Date (Field2)
Calculation Field (Field3)
Value Field (Field4)

If my Anticipated Start date changes I can recalculate/adjust the date for Task One by using a formula using Fields1 and Field4

Here is my ultimate goal. When the Anticipated start date slips I don't want the user to have to go in and individually adjust the dates for all the tasks. If I know Task One happens 3 months after the award, the program should be able to figure out its new date.

my question to the board is; whats the best way to do this? do I create a bunch of fields to store hard values for later use?

 

[Joe4]

So the formula should calculate the number of months between these two date (3)

If I know Task One happens 3 months after the award

I am confused about this 3 month difference. Is it a calculated value (like your first part suggests), or is it a known value (like your second part suggests)?
Or, are you saying that in the original population, you know the Award Date and Task Date and calculate the difference.
And then after that point, if the Award Date changes, the difference ALWAYS stays the same as the original difference, so the Task date is adjusted?

If this is the scenario, in which what field is calculated changes depending on the scenario (initially, the difference, but then after that the Task Date), I would think a VBA solution is you best bet, and make sure ALL data entry and edits are controlled through forms, and no one is updating the data by going directly into the Tables (or that could really mess things up).