Hi - Each week i receive an automated excel file with some key data. However, the data comes in at an hourly level and i would like to roll it up to a day view. I just case about the the day. In the attached workbook you can see it comes in with the date and time and then in 1 cell there is a 1 which indicates the data point i am looking for. Unfortunately, I cannot figure out how to pull the dates into 1 day and be able to keep the data. Thanks in advance for any help.
Sorry think i posted this in the wrong group. Trying to move to the general excel forum but having trouble deleting this.
I have moved this thread for you. But is this not a code issue?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thank you for the help in moving it.
Not necessarily a code issue but stuck trying to consolidate hour time stamps into a single day and looking for some quick tips.
I can't see where you have mocked up what you want. I can see just an empty table ...
Sorry think I did not save the full workbook. The example should be at the bottom. Column G & H start the data entries. Thanks
Sorry - I don't follow. Where is the data meant to be coming from? What do the 1s stand for? You are leaving a lot to the imagination.
Gotcha. The data is coming from a backend service for an iOS application. It indicates that something was started but not completed on those specific dates. The 1's indicate that it happened 1 time during that hour range. It is typically that it would only occur 1 time per day as its user lead. My hope is to clean this report up and make it easier to read at the day view vs having to scroll hour by hour as its usually viewed in a 1 week window. Hope that helps.
This proposal employs two helper columns and a helper row all of which may be moved and/or hidden for aesthetic purposes.
The helper columns (AI:AJ) are populated with the abbreviations for the months and month numbers respectively.
The helper row (78) is populated using: =DATE(--MID(C1,SEARCH(":",C1)-7,4),INDEX($AJ37:$AJ48,MATCH(MID(C1,5,3),$AI37:$AI48,0)),--MID(C1,SEARCH("/",SUBSTITUTE(C1," ","/",2))+1,2))
Cells C83:E85 are populated using: =SUMPRODUCT(($B$37:$B$66=$B83)*($C$78:$AF$78=C$82)*($C$37:$AF$66))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks