Date Range Validation

Jason Voorhies

Based on a few suggestions and other references I calculated a dollar value using the following formula:

=NETWORKDAY(DATE(2023, 7, 1), DATE(2023, 7, 31)) * [Hourly Rate]@row * [Hrs/week]@row / 5

It works well, but I'd really like the formula to reference two separate columns: Start Date and End Date so the column won't calculate if the date is outside of the range.

Here is my use case: Start Date = 1/1/2023 and End Date = 5/30/2023, don't calculate for 6/1/2023 onwards. However, I want the flexibility/capability to adjust the dates and have the formula capture or perform the calculation if the date does move to 6/30/2023. Also, I've got individual month columns based on the first formula provided the give the dollar value.

We've got a very fluid working environment so I'm trying to make the calculations flexible enough to account for quick changes.

Hopefully this makes sense...semi-new to Smartsheets.

Paul McGuinness

Hi @Jason Voorhies

Based on having a start and end date column this formula would do that

=NETWORKDAY([Start date]), [End date]) * [Hourly Rate]@row * [Hrs/week]@row / 5

the formula will just account for whatever date is in the start and end date columns instead of being hard coded into the formula via the Date function.

Hope that helps

Thanks

Paul

Jason Voorhies

@Paul McGuinness Thank you for the guidance. However, it is spitting out an '#UNPARSEABLE' error message. Also, just because it didn't appear to reference the Start/End columns, I had to add the '@row' after both column references. Same error message.

=NETWORKDAY([Start Date]@row), [End Date]@row) * [Hourly Rate]@row * [Hrs/week]@row / 5

Paul McGuinness

Hi @Jason Voorhies

My apologies wrote the formula by hand and omitted the '@row' elements.

You had it fixed it appears there is just an errant close bracket after [Start date]@row

this should work.

=NETWORKDAY([Start Date]@row, [End Date]@row) * [Hourly Rate]@row * [Hrs/week]@row / 5

Did that solve it?

Thanks

Paul

Jason Voorhies

Hi @Paul McGuinness

That did the trick in terms of the '#UNPARSEABLE' error message. Here is the but...

When I copy the formula to subsequent columns, it seems to repeat the same dollar value instead of recalculating for the particular month. To be more specific, if the range Start Date is 3/21/23 and the End Date is 6/15/2023, I'm trying to force a calculation for the specific month (by column). Hopefully the screen capture helps to illustrate the problem more clearly. I'd expect July 2023 to be zero but April through June to have some value.

Sorry if that wasn't more clear.

Paul McGuinness

Hi @Jason Voorhies

Not a problem I see it now, so you require the formula to identify whether any of the networking days fall within the relevant column month and calculate the appropriate percentage of spend.

Example from 15th April to 15th June would roughly equate to half a month of network days in the April and June columns and a full month of network days for the May column.

Looking at your original post I can see that need now but hadn't registered what that was specifically.

Having a think on this one now as you'll need some sort of helper row (hidden) within the sheet to enable each column to identify where it exists compared to the start and end dates so it recognises how much or if it needs to calculate at all.

Let me see if i can come up with something

Thanks

Paul

Paul McGuinness

Hi @Jason Voorhies

Think this may do the trick but it will require some helper columns (Blue).

Formula crosses rows so cant be a column formula in its current state. If you used summary fields instead of the helper columns to hold the first and last day of month reference points, you could referenced those summary fields instead and could then be a column formula. You will need to create the relevant helper columns or summary fields and tweak the formulas to reference them. The specific cell references are fixed so that it can be dragged out to ease creation.

All based on local UK date formatting but should paste fine once you have the columns created.

Column formulas - for reference

March - =IF($[End Date]@row < [ND Mar 23]$1, 0, NETWORKDAYS(MAX($[Start Date]@row, [ND Mar 23]$1), MIN($[End Date]@row, [ND Mar 23]$2)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

April - =IF($[End Date]@row < [ND Mar 23]$1, 0, NETWORKDAYS(MAX($[Start Date]@row, [ND Mar 23]$1), MIN($[End Date]@row, [ND Mar 23]$2)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

May - =IF($[End Date]@row < [ND May 23]$1, 0, NETWORKDAYS(MAX($[Start Date]@row, [ND May 23]$1), MIN($[End Date]@row, [ND May 23]$2)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

June - =IF($[End Date]@row < [ND Jun 23]$1, 0, NETWORKDAYS(MAX($[Start Date]@row, [ND Jun 23]$1), MIN($[End Date]@row, [ND Jun 23]$2)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

July - =IF($[End Date]@row < [ND Jul 23]$1, 0, NETWORKDAYS(MAX($[Start Date]@row, [ND Jul 23]$1), MIN($[End Date]@row, [ND Jul 23]$2)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

Snip of the March formula to see how it sets up

Let me know if that helps?

Thanks

Paul

Jason Voorhies

@Paul McGuinness Getting closer...it is spitting out an  '#UNPARSEABLE' error message again...I must be missing another errant bracket?

Paul McGuinness

Hi @Jason Voorhies

Can you provide a copy of the formula as in your sheet and a snip of the formula where it highlights the columns please?

Thanks

Paul

Jason Voorhies

@Paul McGuinness

Silly of me I should have provided that...

=IF($[End Date]@row < [ND Jul 23]$1, 0, NETWORKDAYS(MAX($[Start Date]@row, [ND Jul 23]$1), MIN($[End Date]@row, [ND Jul 23]$2)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

Paul McGuinness

Hi @Jason Voorhies

Spotted it, the help column is labelled ND July 23, in the formula its [ND Jul 23] (no y) if you update the column name it should work fine.

Just to confirm the helper columns should also be formatted as a date column, they may already be just to check as I didn't mention that earlier.

Let me know if that does the trick.

Thanks

Paul

Jason Voorhies

@Paul McGuinness I never thanked you for your help. This worked perfectly!

Jason Voorhies

@Paul McGuinness OK, maybe I jumped the gun just a bit. Interestingly, it is calculating a negative amount for the month in a few instances:

=IF($[End Date]@row < [ND April 23]$1, 0, NETWORKDAYS(MAX($[Start Date]@row, [ND April 23]$1), MIN($[End Date]@row, [ND April 23]$2)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

Jason Voorhies

@Paul McGuinness I so regret sending that message before I fully investigated the issue. Small typo on my part and it is working great! Thank you again!!

Jason Voorhies

@Paul McGuinness This is almost comical and I apologize. I fixed most of the issue, but a few negative values remain and the issue/answer escape me. The negative dollar values columns are for April and May.

Any thoughts based on what you see in the screenshot below:

Also, including formula:

=IF($[End Date]@row < [ND May 23]$2, 0, NETWORKDAYS(MAX($[Start Date]@row, [ND May 23]$2), MIN($[End Date]@row, [ND May 23]$3)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

Paul McGuinness

Hi @Jason Voorhies

No worries on the thanks, happy to help.

Can you provide a screenshot with the helper column values included please? From what's visible and the formula it should be working as expected, the only variable I cant see are the helper figures, but can see that the row references have moved down a row so assume you've added something in row 1, just looking to check that the first and last days are being referenced correctly and in the right order as that could reverse the formula otherwise.

As the formula is working elsewhere correctly thinking it possibly may be something specific to those two columns (April / May)

Thanks

Paul