•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Sum number of days between given date range that overlap with another date range
Our take
Calculating the number of overlapping days between two date ranges can be challenging, especially with extensive data. To achieve this in Excel, you can use a formula that accurately counts the days within the specified ranges. By identifying the maximum of the start dates and the minimum of the end dates, you can determine the overlap. This approach simplifies the process, allowing you to avoid manual calculations while efficiently managing your data. Let’s explore how to create a formula that addresses your needs effectively.
Hello,
I'm sorry if this post sounds confusing, as english is not my first language.
I am trying to make an excel formula which counts how many days are within two overlaping date ranges (Minimun date with Maximum date and First day with Last day), because the target sheet has hundreds of lines.
I am a bit desperate and i've tried nested IFs:
=IF(B4>=$C$1;IF(AND(B4<>D4;$E$1-D4<0);$E$1-B4+1;IF(AND(B4>$C$1;B4>$E$1);0;C4)))
But i've failed when the First day column has a date bigger than the Minimun day and i am quite lost.
Dates are in the dd/mm/yyyy format.
Is there a way to solve this without having to manually do it?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Trying to calculate the difference in dates using =IF Formula. I am new to ExcelHi, totally new to excel, trying to apply formulas from a previous excel sheet to another. Hello, I'm very new excel and not very good at it. I work a corporate job and I'm trying to calculate the days between two dates(so if M is 1/1/2026 and O is 1/2/2026 i need the difference in column N). I have a formula from a previous spreadsheet and I keep trying to apply it to the new spreadsheet but it keeps coming back as #VALUE!. I also put the formula in Copilot and I can't forgive the life of me figure out the formula or why its not working. I also need for the whole column with different dates. the formula from a previous spreadsheet is =IF(ISBLANK(E139)" "IF(ISBLANK(T39),TODAY()-E139T139-E139)) Hopefully this makes sense. I'm honestly not great with Excel and trying my best. If someone could help that'd be great. Thankyou so much! EDIT: You all have been wonderful! TY!!! submitted by /u/jensinoutaspace [link] [comments]
- Sum of a range based on 2 different criteria?Hello, I'm working on a document which is a holiday funds to keep track of individual person's remaining balance to pay. I want to use a formula to automatically calculate the sum of a range of cells which lie against a certain year under a person's name. Below as an example: https://preview.redd.it/1p19n1o8qrxg1.png?width=991&format=png&auto=webp&s=2e67c0337da92ae1feffe43fc2c7fbaba237d672 As above, I want to look up Name 1 in 1:1, look up Year 1 in A:A and then return the sum of those numbers in that Year 1 range to give me my final result. Is this possible? I've tried all sorts of SUM, SUMIF, SUMIFS, XLOOKUP and combinations but can't seem to get it to work. I also don't want to have to manually select the cells for year SUM as this is constantly updating and want it to be automated. Thank you! submitted by /u/celestialspace [link] [comments]
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- Is there a way to Sumifs up to a certain column with multiple criteria?Hi everyone, I’m looking for a formula to sum all of the columns starting from a particular column up to a particular column. How do I best do that? On the data tab, Row 1 I have the following columns: A1 = date B1 = serial number C1 = customer name D1 = date 1, with the data being $ E1 = date 2, with the data being $ F1 = date 3, with the data being $ In a separate tab I have the same thing mirroring the data tab, but without the $, for the calculation what I intend to do is on E2, I want to say if $A2 = E$ 1, then sum all of D up to E column on the data tab provided that the value on B column of the data tab match B2 of the calc tab, and C column of the data tab match C2 of the calc tab. For F2, it would be the same thing, but sum column D up to column F instead of column E. I hope it makes sense. Thanks for your help! submitted by /u/botng [link] [comments]