Excel: Proportional monthly distribution of sample values over a time period spanning different months
I am trying to get my head around an MS excel formula for a scenario I have.
I am recording discharges over various sample periods. These sample periods will last anywhere from 24 hrs to 1 week. The sample period will be recorded with a start and end time (dd/mm/yy hh:mm).
I want to be able to report the sum discharge total for all sample periods for each calendar month, but ensuring that any sample periods that span two months (i.e. 27/02/24 14:00 >> 05/03/24 14:00) have the total discharge value proportionately distributed between each month total. I acknowledge that this assumes that the discharges are constant rate, which they are for this task. For the time range in brackets, this would be ~34% of the sample period total allocated to Feb-24 as an example.
If anyone can suggest a formula for F3 to satisfy what I've described above, that would be fantastic.
Screenshot attached.
I haven't tried anything so far, because I don't really know where to start with this. I've tried in vain to find a solution online.
You can use the overlap formula
Min(startTime1,startTime2)-max(endTime1,endTime2)
to give you the number of days and hours which fall within the current month. When applied as an array formula in this case it would give you
=LET(dis,C$2:C$4,
startDis,A$2:A$4,
endDis,B$2:B$4,
startMonth,E2,
endMonth,EOMONTH(E2,0)+1,
overlap,IF(endMonth<endDis,endMonth,endDis)-IF(startMonth>startDis,startMonth,startDis),
posOverlap,IF(overlap<0,0,overlap),
SUM(posOverlap*dis/(endDis-startDis)))
Note the use of eomonth(E2,0)+1 which takes you to midnight on the last day of the current month.
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table