Dec 28 2022 08:16 PM
Hello. I have a billing report in Excel that I use to enter claims in a state system. The system requires me to enter a record for each date, along with the number of hours and the total amount. However, if the dates are in sequence, I have to combine them to create a single record entry.
RecipientID | Service Code | Service Date | # Hours | Total Hours | Amount | Total Amt |
304423379 | W7224 | 12/1/2022 | 7.00 | 7.00 | $48.58 | 48.58 |
304423379 | W7224 | 12/5/2022 | 7.00 | $48.58 | ||
304423379 | W7224 | 12/6/2022 | 5.00 | $34.70 | ||
304423379 | W7224 | 12/7/2022 | 7.00 | $48.58 | ||
304423379 | W7224 | 12/8/2022 | 5.00 | 24.00 | $34.70 | $166.56 |
304423379 | W7224 | 12/12/2022 | 6.00 | $41.64 | ||
304423379 | W7224 | 12/13/2022 | 5.00 | $34.70 | ||
304423379 | W7224 | 12/14/2022 | 7.00 | 18.00 | $48.58 | $124.92 |
304423379 | W7224 | 12/19/2022 | 7.00 | $48.58 | ||
304423379 | W7224 | 12/20/2022 | 7.00 | $48.58 | ||
304423379 | W7224 | 12/21/2022 | 7.00 | $48.58 | ||
304423379 | W7224 | 12/22/2022 | 7.00 | 28.00 | $48.58 | $194.32 |
The data above would be entered as four (4) records, as follows:
Date From | Date To | Hours | Amount |
12/1/2022 | 12/1/2022 | 7.00 | $48.58 |
12/5/2022 | 12/8/2022 | 24.00 | $166.56 |
12/12/2022 | 12/14/2022 | 18.00 | $124.92 |
12/19/2022 | 12/22/2022 | 28.00 | $194.32 |
Is there any way for Excel to calculate the sequential dates data without my having to insert the Auto Sum for each sequence, manually? My reports cover one month and can be upwards of 500 rows. Thank you.
Dec 28 2022 09:58 PM
Take a look on a sample file.
All you need to do is input your "Date From" and "Date To"
and it will automatically calculate the hours and amounts within that date.
Jan 03 2023 05:58 AM
Jan 03 2023 08:14 AM - edited Jan 03 2023 09:21 AM
If you're on 365 with access to LAMBDA, there's an elegant solution available.
First, I created dynamic ranges for Service date, hours, and amount.
Created a named item for the Header:
={"Date From", "Date To", "Hours", "Amount"}
Obtained the Week Number:
=MAP(sdate, LAMBDA(e, WEEKNUM(e)))
Summarize LAMBDA:
=LAMBDA(a,v,LET(
f, FILTER(HSTACK(sdate, hours, amount), WNum = v),
VSTACK(a, HSTACK(MIN(TAKE(f, , 1)), MAX(f), SUM(TAKE(DROP(f, , 1), , 1)), SUM(TAKE(f, , -1))))
))
Sheet level formula:
=REDUCE(Header,UNIQUE(WNum),Summarize)
Jan 03 2023 09:30 AM
Hi @gdougher (Happy New Year too)
My Access dBase ... Maybe I need to look at the whole process from another angle
#1 Instead of copy/paste from Access to Excel, why not using Get & Tranform (from Excel) to get the data from Access
#2 Once the records are gathered, aggregate as desired with Power Query and return the output to an Excel sheet:
The attached sample doesn't get the data from Access but it's fairly easy to setup
Any question feel free to ask