I need help in writing a formula

Nike_0727

Hi Team, I need help in writing a formula. I have 2 Date Columns (Drop-off Date & Actual Termination Date). I need to calculate the no of days the item stayed with me for the current month. Example if "":Drop-off date"" is 27th May and ""Actual Termination Date"" is 18th of June then for the current month the answer should be 18. And every coming month the value should get updated automatically for that month. One more ex - If Drop-off Date is 2nd June and Actual Termination Date is 8th June then the answer shall be 6 days for the current month. Please help

rshelnutt

I found this formula in the community forum:

=Networkdays([start date]1, [end date]1)

Just plug in your titles for start date and end date. I always capitalize my titles - not sure if that is needed or not, but it works.

However, when I tested it, it seemed like it was off by 1 day. It counts only the days between the 2 days. So, I think it depends on how you count your days. For me, I would want to include the finished date, so I wrote it to add 1. Here is what I used in my file:

=NETWORKDAYS(Start1, Finish1) + 1

See if either of those help you. I'm still very much a learner here myself but have found many answers through the community.

Let me know if it works.

Take care,

Regina

Eric Law

@Nike_0727

You will probably need a few helper rows. Also, do these typically last longer than 2 months? If so, that would be harder, if not, then you are good.

You will need 5 columns total.

1) You will need a helper column for end of month, here is the formula, it takes the next month's first day and subtracts 1 to get the end.

=IFERROR(DATE(YEAR([Drop-off date]@row), MONTH([Drop-off date]@row) + 1, 1), DATE(YEAR([Drop-off date]@row) + 1, 1, 1)) - 1

2) Subtract Drop-off date from that column, call it "Submit to EOM" or whatever

3) Subtract Actual and Drop-Off date, call it "Gross Days"

4) Subtract "Gross days" from "Submit to EOM", Call it "Next Month Days"

5) Last column will be your results, call it "Current Month Amount". Formula can be the following,

=IF([Next Month Days]@row < 1, [Gross Days]@row, [Next Month Days]@row)

I know that's a lot but hope it helps!