Making Dates auto adjust using formulas

[needexcelhelp2020]

Hey there, I know excel but when the formulas come I am totally lost. My logistics manager is also very busy and he cannot keep checking these. What I need is a way to take the value from the following

COLUMN A (Arrival Date)

COLUMN B (Departure Date)

and put the NIGHTS (these are not time dependent, they just have to be correct values) into

COLUMN F

so this date to this date = this many nights

this is super crucial you are looking at a glimpse of a very large trip , and the nights have to be correct, so that the schedule is followed.

I also would love if there was a way so that if I altered the dates the rest would auto adjust, say i changed the range of

ORIGINAL

March 28 2020 - April 1 2020, this should equal 4 nights (the way we calculate these "nights" is the day of arrival (the 28th of march) is NIGHT 1 and the departure day is after the final night. so arriving on March 28 and staying 4 nights, leaving on the morning of the 5th day would be march 28-April 1. If I changed that date to leave on March 31 as the departure date, I would now only have 3 nights, however every other date after that would be incorrect, and were talking 10,000 + entries,

am I going crazy, is there a way to autocalculate everytime I change date ranges so the rest stay the same, I am thinking no but I had to ask.

Thanks for all the help in advance. A tiny section of the workbook is attached. You will see line 7 is already wrong, after you go through these manually enough you start missing all sorts of errors!

[Pete_UK]

You can use this formula:

=B4-A4

Format as General, then copy down.

Hope this helps.

Pete

[AliGW]

Administrative Note:

Welcome to the forum.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[needexcelhelp2020]

You can use this formula:

=B4-A4

Format as General, then copy down.

Hope this helps.

Pete

Pete,

Could you help me understand how to implement this formula, and what exactly it does?

[needexcelhelp2020]

Administrative Note:For instance, you might in the future post questions which are related to your regional settings.

Ali

I totally understand and will update my location if I need "regional specific" information or help

Thanks

[jeffreybrown]

Also, take some time to review our forum rules about thread titles.

Hello & Welcome to the Forum,

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Your current thread title is not very descriptive. It should resemble terms you would use if searching Google for an answer. As it is, your title would produce thousands of useless hits.
• Please take a moment to amend your thread title according to Forum Rule #1 and please make sure the title properly describes your request.

[needexcelhelp2020]

Updated the subject title per this forums excessive rules!

[FlameRetired]

Not excessive. Reasonable and considerate.

[needexcelhelp2020]

Pete,

Could you help me understand how to implement this formula, and what exactly it does?

never mind I have the first part working, the second part will be much tougher!

[Pete_UK]

Pete,

Could you help me understand how to implement this formula, and what exactly it does?

You type that formula into the first cell where you want the result. I was unsure whether this should be in cell F4 (which you refer to in your first post, and above which you have the title DURATION), or in cell E4, where you have numbers already. Then you can copy the formula down to the bottom of your list of activities. If you want the result to return the text "nights" as well as the number, then you can apply a custom format to all those cells of:

0" nights"

The formula subtracts one date from the other one, and thus results in the number of elapsed days (or nights in your case) between the two. Dates are stored in Excel as the number of days since some reference date (of 1st January 1900), so you can subtract them like this as they are just numbers to Excel.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

P.P.S. These are not "rules" as such - just courtesy behaviour to help other members of the forum in searching for solutions to problems.

Pete

[Pete_UK]

The second part of your request, i.e. to automatically adjust the number of nights if a date is changed, will happen automatically with the formula that I gave you.

Pete