SOLVED
Home

Calculate the numbers of days from date to date.

%3CLINGO-SUB%20id%3D%22lingo-sub-748300%22%20slang%3D%22en-US%22%3ECalculate%20the%20numbers%20of%20days%20from%20date%20to%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748300%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20needing%20help%20calculating%20between%20two%20dates.%20I'm%20using%20the%20%3DDAY(end_date%2C%20start_date)%2C%20however%2C%20I%20need%20the%20end%20and%20start%20dates%20to%20be%20included%20in%20the%20day%20count.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20If%20our%20start%20day%20is%20on%207%2F01%20and%20our%20end%20day%20is%20on%207%2F24.%20Using%20the%20above%20formula%20gives%20me%2023%20days%2C%20however%20the%20job%20lasted%2024%20days%20because%207%2F01%20was%20the%20first%20day%20on%20the%20job%20and%207%2F24%20was%20the%20last%20day%20on%20the%20job.%20I%20wondered%20if%20there%20was%20a%20easier%20way%20for%20me%20to%20calculate%20this%2C%20or%20if%20I%20needed%20to%20just%20put%20a%20%2B1%20after%20my%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20Advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-748300%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-748557%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20the%20numbers%20of%20days%20from%20date%20to%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748557%22%20slang%3D%22en-US%22%3EIn%20law%20and%20accounting%2C%20the%20start%20date%20is%20excluded%2C%20and%20the%20end%20date%20is%20included.%20So%20is%20Excel%2C%20such%20that%20you%20simply%20have%20to%20add%201%2C%20as%20you%20correctly%20suggested%2C%20to%20include%20both%20in%20the%20number%20of%20days.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-748559%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20the%20numbers%20of%20days%20from%20date%20to%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748559%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20help!%20I%20will%20add%20one%20to%20my%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-748563%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20the%20numbers%20of%20days%20from%20date%20to%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748563%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome.%20Beware%20that%20the%20addition%20of%201%20is%20limited%20only%20to%20satisfy%20your%20requirement.%20Other%20than%20your%20requirement%2C%20the%20addition%20of%201%20is%20precluded.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-802090%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20the%20numbers%20of%20days%20from%20date%20to%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802090%22%20slang%3D%22en-US%22%3EThe%20inclusion%20of%20Start%20Date%20and%20End%20Date%20is%20commonly%20used%20in%20utility%20billing%3B%20I.E.%20all%20days%20the%20service%20is%20provided%20are%20included%20in%20the%20bill.%20As%20such%2C%20including%2C%20both%2C%20Start%20and%20End%20dates%20is%20not%20an%20uncommon%20need.%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20such%20a%20common%20need%20it%20is%20surprising%20that%20Excel%20does%20not%20provide%20a%20cell%20function%20for%20this%20purpose%20(without%20having%20to%20modify%20an%20existing%20cell%20function).%3C%2FLINGO-BODY%3E
OffMng
New Contributor

Hello,

 

I am needing help calculating between two dates. I'm using the =DAY(end_date, start_date), however, I need the end and start dates to be included in the day count. 

 

For example, If our start day is on 7/01 and our end day is on 7/24. Using the above formula gives me 23 days, however the job lasted 24 days because 7/01 was the first day on the job and 7/24 was the last day on the job. I wondered if there was a easier way for me to calculate this, or if I needed to just put a +1 after my formula.

 

Thank you in Advance!

4 Replies
Solution
In law and accounting, the start date is excluded, and the end date is included. So is Excel, such that you simply have to add 1, as you correctly suggested, to include both in the number of days.

@Twifoo Thank you for your help! I will add one to my formula.

You’re welcome. Beware that the addition of 1 is limited only to satisfy your requirement. Other than your requirement, the addition of 1 is precluded.
The inclusion of Start Date and End Date is commonly used in utility billing; I.E. all days the service is provided are included in the bill. As such, including, both, Start and End dates is not an uncommon need.

With such a common need it is surprising that Excel does not provide a cell function for this purpose (without having to modify an existing cell function).
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies