Date Range Validation

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

@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

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

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.

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

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

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

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

@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)

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

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

@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)

@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!!

@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)