I have a table named "Resolved Request", in which I have two specific columns: Date Assigned and Date Resolved. I have a third column named Time Spent Resolving, which should be the amount of time between Date Assigned and Date Resolved, in days hours and minutes. I am using MS Access 2013 but the file I am modifying has to be compatible with MS Access 2007.
I know how to calculate the difference between two dates (Date1-Date2 as calculated column), however I cannot get the format I want. I need for example to have Time Spent Resolving show up as "34 days, 2 hours and 5 minutes". How would I retrieve the amount of time between two dates in that kind of format within MS Access?
Also as an added bonus question, the "Date Assigned" field is a duplicate of a field I have in another table named "In Progress Request". Would I be able to calculate the difference between "Date Resolved" in "Resolved Request" and "Date Assigned" in "In Progress Request"? If not, could I somehow link both Date Assigned fields in both tables to update each other automatically?
Here is the formula that you need to display the format that you require (34 days, 2 hours and 5 minutes). I don't think you should have this formula for a calculated column as it's a waste of space in your table.
Replace txtInsertDateTime and txtAuditDateTime in the formula with the columns that you have.
=CStr((DateDiff("n",[txtInsertDateTime],[txtAuditDateTime])\60)\24) & " days," & CStr((DateDiff("n",[txtInsertDateTime],[txtAuditDateTime])/60) Mod 24) & " hours and " & CStr(DateDiff("n",[txtInsertDateTime],[txtAuditDateTime]) Mod 60) & " minutes"
Not sure about your bonus question, so cannot answer.