Trying to use Time Function to calculate how many days,hours,minutes
I am using this found in the functions list:
But Error says #INVALID DATA TYPE. Is that because the columns i am referencing are cross reference formulas and not text/number types?
SEE BELOW FORMULA AND SHEET:
=DATEONLY([Pending Tech Response Date/Time Stamp]@row) - DATEONLY([Completed Date/Time Stamp]@row) + " day(s), " + " " + ROUNDDOWN((TIME(RIGHT([Pending Tech Response Date/Time Stamp]@row, 8)) - TIME(RIGHT([Completed Date/Time Stamp]@row, 8))) * 24) + " hour(s) and " + (((TIME(RIGHT([Pending Tech Response Date/Time Stamp]@row, 8)) - TIME(RIGHT([Completed Date/Time Stamp]@row, 8))) * 24) - ROUNDDOWN((TIME(RIGHT([Pending Tech Response Date/Time Stamp]@row, 8)) - TIME(RIGHT([Completed Date/Time Stamp]@row, 8))) * 24)) * 60 + " minutes"
Answers
-
Your Pending Tech... and Completed Date... should be system generated fields with dates and times, your formula can go into a Text/Number type.
I just tried and it looks good to me:
-
@KPH can you clarify what you mean? my pending tech and completed dates are not system generated. This work around is the biggest most ridiculous thing I've ever had to make work.
I had to set an automation to send each row to helper sheets every time each row (request) goes to a different status. That way I could capture that date/time stamp. Once on that helper sheet, I bring that date/time stamp back into the main sheet using Index/Match formulas (example below):
=IFERROR(INDEX({Tech Support Time Analysis Pending Tech Range 1}, MATCH([Request #]@row, {Tech Support Time Analysis Pending Tech Range 2}, 0)) + "", "")
Now that I have the above formula in my main sheet, I want to capture times it takes to go between each status.
-
Oh boy! So they kind of are system generated dates that you are moving about. Which makes them look like the system generated ones (enough to fool me 😉) but when you try to use them like a system generated column something weird is going on. TBH I have no idea what the correct data type is in this situation. I quickly replicated what you are doing and have the same struggle. I can use the DATEONLY formula on system generated columns but when I pull a system generated date/time into another column, I cannot get the DATEONLY function to work.
I will have a prolonged ponder but this is not something I am familiar with. I just didn't want to leave you hanging.
-
@KPH I appreciate you're help in trying to brainstorm for sure. Thank you : ) I think i'm going to have to split it up to a 'date only' column and then a 'time' column.... for each status. WHICH IS CRAZY. I've been trying to get this project set up since October and I can't wait to never look at it again lol.
-
@Krystal Garcia If I have any brainwaves, I will let you know. Hopefully, someone who has done this will see and know exactly what is needed. What you want doesn't see too unreasonable.
-
It sounds like your INDEX & MATCH formulas are both pointing at system column(s) such as modified (date). If that's the case you can actually subtract one INDEX & MATCH from the other to get the time between the timestamps in days; though you would need to remove the +"" from them that changes the date/time into text. Given the formula you provided, that would look something like this:
=IFERROR(INDEX({Tech Support Time Analysis Complete Range 1}, MATCH([Request #]@row, {Tech Support Time Analysis Complete Range 2}, 0)) - INDEX({Tech Support Time Analysis Pending Tech Range 1}, MATCH([Request #]@row, {Tech Support Time Analysis Pending Tech Range 2}, 0)), "")
That formula would return the time in days with a decimal for partial days. So 1.25 would be 1 day 6 hours. If a formula like that is placed as a column formula in a column named "Total Time" on the sheet, you can use a formula like the following to show the Days, Hours and Minutes:
=INT([Total Time]@row) +" day(s), " + INT(MOD([Total Time]@row, 1) * 24) + " hour(s) and " + INT(MOD(MOD([Total Time]@row, 1) * 24, 1) * 60) + " minute(s)"
The first INT returns just the whole number of days. The first MOD returns the decimal from the number of days only, then is multiplied by 24 to convert to hours and again placed in an INT to return only the whole number of hours. Finally, we use one MOD to pull only the partial day multiply that that 24 to convert to hours then we use another MOD to return only the decimal from the hours and multiply that times 60 to convert to minutes and wrap that in INT to again return only the integer.
All formulas were typed directly here so there's a strong possibility that a parenthesis or comma is out of place.
