Date Format Formula

katie.mcelroy

My org has a sheet that logs work orders by date received. The date format is 01-03-2022 08:47:13 AM.

I have formulas on another sheet to count the total number of work orders per quarter.

=COUNTIFS({WO Log Range 2}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))

The formulas aren't working. I think it's because of the format used in the Date WO Created column but I can't change the format, it's used by other depts.

Is there a work around to this or is my formula wrong?

jmyzk_cloudsmart_jp

@katie.mcelroy

Please try this.

=COUNTIFS({WO Log Range 2}, @cell >= DATE(2023, 1, 1), {Created}, @cell <=  DATE(2023, 3, 31))

katie.mcelroy

Thank you but that's still giving me an invalid ref. {WO Log Range 2} in my formula below is the Date WO Created column reference.

=COUNTIFS({WO Log Range 2}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))

The formula worked before the date format changed a few days ago. I was thinking of adding a column to the WO log sheet that converts the Date WO Created date to a date format that smartsheet will recognize but I'm not sure what that formula would be.

Paul Newcome

Is the Date WO Created column a system generated Created (date) type column, or is it a text/number column being populated with a text string?

If the first, then your formula should be working. If the second, then you are on the right track with needing a helper column to grab an actual date value out of it.

=DATE(VALUE(MID([Date WO Created]@row, 7, 4)), VALUE(LEFT([Date WO Created]@row, 2)), VALUE(MID([Date WO Created]@row, 4, 2)))

katie.mcelroy

The Date WO Created column type is Date. They change the format but I'm still getting errors.

If I change my column to a Date type it's returning an error of Date Expected. If I set it to a text type it doesn't pull in any values.

=COUNTIFS({WO Log - Date WO Created}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))

Paul Newcome

You need to use the formula I provided above to strip out the date portion of the text string and place it into a date type column. Then you can reference this helper column in your COUNTIFS.

katie.mcelroy

That worked but now I'm running into the problem where sometimes the date is not always the same position since they changed the format.

=DATE(VALUE(MID([Date WO Created]@row, 6, 4)), VALUE(LEFT([Date WO Created]@row, 1)), VALUE(MID([Date WO Created]@row, 3, 2)))

Paul Newcome

I see. Try this:

=DATE(VALUE("20" + MID([Date WO Created]@row, FIND("/", [Date WO Created]@row, FIND("/", [Date WO Created]@row) + 1) + 1, 2)), VALUE(LEFT([Date WO Created]@row, FIND("/", [Date WO Created]@row) - 1)), VALUE(MID([Date WO Created]@row, FIND("/", [Date WO Created]@row) + 1, FIND("/", [Date WO Created]@row, FIND("/", [Date WO Created]@row) + 1) - (FIND("/", [Date WO Created]@row) + 1))))

katie.mcelroy

That worked to pull the correct date into the helper column but my sheet that is pulling from the helper column is still not pulling the correct data.

=COUNTIFS({WO Log - Date Conversion}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))

Paul Newcome

Where exactly is your COUNTIFS in the second screenshot?

katie.mcelroy

It's on a separate sheet (WO Totals) from the sheet that has the date conversion column. The date conversion column is a date type. The sheet with the totals is text type for all columns.

katie.mcelroy

Paul Newcome

Right. But which cell is it in? Based on your screenshots, the COUNTIFS formula you have posted would be outputting a zero.

katie.mcelroy

It's in Q1, Q2, Q3, Q4 columns of row 2. The count should be over 30 but it's not pulling in the right number.

I also tried using a formula in the sheet summary instead of referencing the sheet and I'm I'm getting an error #INVALID VALUE

Paul Newcome

All of the dates in your [Date Conversion] column of your most recent screenshot are 2020. Not 2023.

katie.mcelroy

The screens hot is just a small portion of the sheet. There are work orders that go back to 2020 but there are over 30 lines that have 2023 from Jan-mar listed.