Originally Posted by
atchorizon
the attached file is a typical extract from the system with highlighted columns needs to be calculated.
See the formulas in columns K and P in the attached file. I had to truncate the file to 2000 rows in order to fit the attachment limit. 
(Caveat: The original attachment had freeze-top set. I attempted to replace it. But if you find the top row (titles) is "frozen", you can unfreeze by clicking View, Freeze Panes, Unfreeze Panes.)
I leave it to you to copy and modify the formulas into columns L:N and O.
Note the formula in O2 and P2 is array-entered by selecting a single cell and pressing ctrl+shift+Enter instead of just Enter.
Since I did not find month numbers for each date/time (dd/hhmm), I implemented the following heuristic.
In K2 (essentially =I2-G2), if I2 is 01/xxxx and G2 28/xxxx or greater, I assume that I2 is an appropriate month before a month with 28, 29, 30 or 31 days.
In P2 (essentially =H2-Y2), if H2 is 01/xxxx, I try to intuit the current month based on the max month in G2:J2 and Q2:T2, just in case subtracting Y2 minutes would result in the last day of the previous month.
I'll try to explain the theory of operation of each formula. Ask questions, if you need clarification.
K2:
The text date in the form dd/hhmm is converted into numeric Excel date/time using DATE(year,month,day)+TEXT("hhmm","00\:00").
The year is 2015 or 2016, an arbitrary normal and leap year. We choose 2016 if I2 is day 01 and G2 is day 28 to 31.
The month is July, an arbitrary 31-day month, unless I2 is 01 and G2 is day 28 to 31. In that case, G2 is month 2, 6 or 7, arbitrary months with the correct number of days; and I2 is the next month.
The two numeric Excel date/times are subtracted, then multiplied by 1440 to convert to a number of minutes. The calculation is rounded to an integer in order to avoid arithmetic anomalies that occur with non-integer values (time of day) that are represented internally using 64-bit binary floating-point.
P2:
Again, the text date in the form dd/hhmm in H2 is converted into numeric Excel date/time using DATE(year,month,day)+TEXT("hhmm","00\:00").
Also, the number of minutes in Y2 is converted into numeric Excel time (date=0).
For H2, the year is 2016 if the max day in G2:I2 and Q2:T2 is 29; otherwise, the year is 2015.
The month is 8, an arbitrary 31-day month, unless the max day in G2:I2 and Q2:T2 is 28 to 30. In that case, the month is 3 or 7, so the day resulting from the calculation might be the last day of the previous month (2 or 6).
I hope these assumptions meet your needs.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks