-
How can I convert a referenced created date to a numeric value in order to calculate time lapsed
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…
-
Age Formula Keeps Rounding Up
This is my formula: =IFERROR(YEAR(TODAY()) - YEAR([Date of Birth]@row - IF(TODAY() < DATE(YEAR(TODAY()), MONTH([Date of Birth]@row)), 1, 0)), "") It keeps rounding to the next age. For example, 04/09/1978 is showing as 47 but it should say 46. When I add decimals it says 47.00. I also need the age to only populate when…
-
How to flag duplicate dates within a range
Hi everyone, I'm attempting to build an annual leave tracker for my team. The current build is the employee completes a form with Start and End dates > this updates on a Request Log for managers to Approve / Decline > once Approved, copied to a master log and a separate Tracker sheet keeps a list of all employees, their…
-
Concatenate formula to combine Date and Time into unique code
When I use a concatenate formula to combine a Date and Time to create an individual code, the Date appears in the default format 01/01/25 but the date in the Column it is referencing is formatted 2025-01-01 which is how I need it to appear in the result. How can get the Concatenate formula to just reproduce the Date format…
-
How to fix adding 1 or -1 to date difference calculations
Hello, I'm trying to set up a formula which will calculate the difference between two dates to achieve a days overdue number for our orders. Current formula is: =IF(Status@row = "Delivered", NETWORKDAYS([Delivery Date Expected]@row, [Delivered Date]@row)) This is returning an almost correct result, except it always adds a…
-
Identify Most Recent Record
Hello, I am using SmartSheet to log Training Records. Every time a record is submitted, I am recording three fields: Date, Name, Topic. Based on the date, I am projecting forward the next time training needs to occur. (ie: 3 years after most recent training record). This occurs in a "Renewal Date" Column as a column…
-
Convert day of month to Business Day date
We have a calendar template that we re-use every month. On this calendar ,we have a column that is the numerical representation of the day of the month (1, 2, 3, 4, etc.). Each month, this is a different date. Currently, someone is manually entering the dates that correspond to the day number. Is there a formula that would…
-
Track Form Submissions By Date
My organization has several forms set up that are utilized quite a bit. I'd like to track how many forms are submitted on a given day, week, month, quarter. I have tried countifs, and a whole bunch of other formulas. I also made a report but I am unable to pull report metrics into a dashboard so that it can visualized…
-
Add months to a date
Hi Team, I need to create a formula to add X months to a date (X) to calculate a due date. Any suggestions? Thanks in advance!
-
Need to update dates to Q1_2025, Q2_2025, Q3_2025, Q4_2025
I need to update a value in a new column. How would I look at a date column that shows dates between 01/01/2025 to 03/31/2025 and place the value of Q1_2025 into another cell?