How can I convert a referenced created date to a numeric value in order to calculate time lapsed

ErnestVASA

I reference two created date fields from another sheet. I use the following formula to reference the created date:

=IFERROR(INDEX({2a. FEMALE CHANGE ROOM Range 1}, MATCH(JOIN@row, {2a. FEMALE CHANGE ROOM Range 2}, 0))+ "", "")

The result is displayed as 10/01/25 10:35 PM which is correct.

My problem: I need to calculate duration/time lapsed between two referenced created dates, but I get a #INVALID COLUMN VALUE error regardless of the formula I use (Netdays, networkdays, A-B or whatever I try). This is seemingly due to the referenced result not being recognised as a numeric value. I trawled the net and all suggestions to convert to numeric value does not work.

Ive tried the following formula to calculate time lapsed between the two created date references, but I also get the same error #INVALID COLUMN VALUE because the referenced date is not recognised although it is displayed accurately:

=IF(AND([DATE & TIME OF SUBMISSION PART 2]@row <> "", [DATE & TIME OF SUBMISSION PART 1]@row <> ""), ([DATE & TIME OF SUBMISSION PART 1]@row - [DATE & TIME OF SUBMISSION PART 2]@row), "")

Does anyone have any usable solution for me?

Paul Newcome

Are you putting this formula in a date type column?

ErnestVASA

Hi Paul - No - as it does not show the time portion of the result, which is needed in my calculation - as I need to show the exact time (hours & minutes) in the calculated result.

Paul Newcome

In that case, end it with "adding" empty quotes to turn it into a text string.

=existing_formula + ""

ErnestVASA

Thanks. I already have this in my formula, and I believe this "fix" in part is the cause of the problem. (see the first formula in my question at the top of this dialogue.

1. Adding the +"" ensures it shows the date and time as needed, but this turns the result in a text string, causing my formula referencing this result to show an error.
2. I need a formula or "way" to turn this result back into a numeric string for any formula to operate correctly.

To clarify. Sheets a, b, and c are referenced and their "created date" fields are referenced in a "lookup" sheet.

In the lookup sheet, I need to calculate the time elapsed between references a, b, and c - as all three reflects the date and time (created date). Due toe Smartsheets limitation to reflect the referenced dates correctly, I use the +"" in my lookup formula (either index match or vlookup), turning the results into a text string to show it accurately, but this results in any formula using the referenced cell to fail.

Question: How do I turn this text string back into a numeric string in order to calculate duration between these three references (a, b, and c) in my lookup sheet?

Paul Newcome

This thread should have some formulas you can use (not sure why it won't create the hyperlink but should be able to copy/paste it into a browser tab):

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time/p1

.