-
Select Most Recent Date In List
Hello! I need a formula to pull the most recent date in a list, but I keep getting unparsable and invalid errors. Could someone please help me create a formula to put in the highlighted cell? Please note there are empty dates (like the top row). This sheet is driven by a form, so new dates may be added out of order - in…
-
Display date and duration by referencing other cells in the same sheet
Hello Everyone, I'm trying to reference data from row 44 into row 3 in the same sheet. "=[Task Name]44" works like a charm (see below). However, =[Start]44 doesn't result in the date "08/13/25" being reflected in row 3. Likewise, =[Finish]@44 doesn't reflect the Finish date from row 44. =[Duration]@44 or =Duration44…
-
Different Dates on the one sheet
I am in Victoria, Australia and I am using a form to populate a sheet. I want to automatically populate the Inspection Date to be the date the row was created. I am using the formula =created@row The Created date, being the system generated date is correct, but as you can see the Inspection date using the above formula is…
-
Linking my pacing sheet to a dashboard - if the date column is Today, return the total hours for the
Hello, I am trying to set up metrics for a lab testing schedule. I have created a pacing sheet which is set up to show how many hours of testing each analyst is assigned that day. The left column is the date, and the column next to it is the total hours assigned for the day. I would like my dashboard to show the total…
-
How can I display the current month number as a 2-digit number so it matches my year-month?
My ultimate goal is to create an automation that will look at the total audits completed for the current month and if the number <10, a notification will be sent. In order to do that, I need the 'current month' and 'current year' formats to match the 'Month' and 'Year' formats. I have tried =TEXT(TODAY(), "MM") but get an…
-
"Substitute" Column Formula Inconsistency
Hello, I am using the column formula below to extract the time from the "Created Date" column. =SUBSTITUTE([Date Submitted]@row, DATEONLY([Date Submitted]@row), "") However, you can see in the screen shot below that it doesn't always work. Is this a bug?
-
Suggestions for making readable schedules / Conditional formatting last occurrance
Hello All, I have a manufacturing schedule sheet which lists tasks that are sorted by date. I have a bunch of conditional formatting rules applied to make things easier to read and identify. Each day contains 5 or more tasks on average. The supervisors who populate the schedule are having trouble when editing rows because…
-
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…
