•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Time Tracking and reporting with Power Automate
Our take
Managing time effectively is crucial, especially when juggling multiple projects. For the past four months, I’ve utilized a smart Excel table to track my working hours, capturing essential details like date, time, and project activities. With a pivot table summarizing my hours, I gain valuable insights into daily project time allocation. Now, I aim to streamline my reporting process using Power Automate. I seek guidance on filtering with Office Scripts and exporting my final report as a PDF, enhancing my efficiency without additional manual tasks.
Since 4 Months I use Excel to track my working hours.
It is a simple intelligent Table where I add current Date, Time, Project im working on and what I am doing. A "duration" column will subtract the entered Time from the Time of the following entry.
Since I jump a lot between Projects this Table helped me a lot to track my time to the Minute.
A Pivot Table converts these entrys in bigger chunks and presents me with an overview how much time was spent on each Project per day. Everything works perfect.
Now I need to send a report of my Time to a co-worker, who will enter it in our ERP Software.
I want to automate this process, so that I don't have to do anything additional to tracking my time. I want to use Power Automate I guess.
I allready tweaked the Overview on a second page, to round the final time track to whole 15 minutes and subtrakt breaks and filter out unnecassary entrys (I always have to enter the End of the Workday with 0 duration, for the last entry before that to caluclate its duration).
I tried to use office scripts to do this filtering for me, but it doesn't work relyable. It will often reactivate allready filtered entrys when adding to the filter.
Do you have some advice how to filter with office scrips in a pivot table? I used Makros before office scripts, and there it worked flawlessly. But Makros can't be used by Power Automate.
And how do I export the final Report Sheet as PDF in Power Automate, without exporting the whole Table with every single dataentry?
I spent hours trying to get this to work, using copilot for coding, but it wasn't very helpfull. Copilot generates error after error.
Edit: I use Excel 365
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Excel Power Automation - Sending Emails to Users Based on Sheet CriteriaHi All! My organization currently uses a website called Smartsheets to create task lists. I've created automations that run each morning that send emails to users if a task becomes past due. It fulfills the intended purpose; however, every single month there seems to be some new issue; therefore, I'd like to transfer over to using functionality in native Excel. My research turned me on to Power Automation. I found the tools I needed until I hit a roadblock: P.A. does not look at the contact within the sheet and send a message to that person. It appears to just go to one designated individual. Any thoughts, recommendations, or solutions? I am almost at the end of my rope and ready to just deal with Smartsheets. A screenshot of our task list can be found in the comments. Maybe it provides some reference that I missed. Thanks! submitted by /u/bltsmith [link] [comments]
- How do you properly hand over Office Scripts or trigger Power Automate flows from Excel without relying on personal OneDrive?Hi everyone, I’m running into a problem and I hope someone here has experience with this. I was asked by another department to automate one of their workflows in Excel. So far so good. I didn’t want to use VBA because our company is slowly phasing it out, so I took this as an opportunity to gain experience with Office Scripts. The script itself is finished. It can be triggered via a button and it works as intended. However, I realized that I don’t want the script to live in my personal OneDrive “Documents/Office Scripts” folder. I want to hand it over to the department so they can use it independently. My idea was to create a subfolder called “Scripts” next to the Excel file and store the scripts there. But here’s the problem: I have no idea how to reference or trigger those scripts from Excel. Excel seems to only recognize scripts stored in the personal Documents folder. Even if I manually place the script there (tested with a separate account), Excel still doesn’t detect it. So I thought about triggering the script through Power Automate instead. Surprisingly, that works well — and PA doesn’t care where the script file is stored. BUT now I have a new issue: How do I link the flow back to the Excel file? Ideally, I want a button inside Excel that starts the flow. The users of this file are not technical at all, so it needs to be as simple as possible. I couldn’t find any way to connect a button in Excel to a Power Automate flow — except by using Office Scripts again, which would put me back in the same situation where the script must be stored in my personal OneDrive… which defeats the whole purpose. Has anyone dealt with this before? How do you hand over Office Scripts or trigger flows in a clean, future‑proof way so everything keeps working even if you’re no longer around? Thanks a lot for your help! PS. This text was translated and optimized with AI submitted by /u/MR_Datenanalyse [link] [comments]
- Creating an auto-populating visual calendar for multiple departments to view?I coordinate student schedules across multiple hospital units. I log the shifts in an hours log spreadsheet (date, start time, end time, unit, student name, etc.). Every month, I need to send each unit a schedule showing who is coming in and when so they can review and plan coverage. Right now, this part is very manual, and does not lend to tracking any data like how many students on each units, hours, etc., which we need as a department. We have over 100 students each month, so as much automizing as possible would be helpful as its a big process. Ideally, I’d like something that: Pulls directly from the hours log (no retyping or copy/paste) Is easy to read at a glance for unit managers Can handle many units, many months, and many students Lets me filter by unit + month I'm not really an excel genius, and I've been using copilot to help me with some of this and formulations, but It's not providing me with accurate formulas for the actual calendar conversion. If there are any resources on what formulas to use, what platforms that I could maybe look into for how to do this, or just honestly any advice at all. We've gotten the data tracking mostly drafted up and it's able to pull from pivot tables and show the analytics, but the calendar is my biggest hurdle at the moment. submitted by /u/JuggernautFlashy6489 [link] [comments]
- Creating Pivot Table from Multiple SheetsHi All, I'm working on a large tracking workbook, consisting of several clinical trials in order to track by patient detail the payments we are owed by the funder, what we have received, and the difference. All these payments are delayed by 3m-2 years in some cases and leadership wants to accurately predict how much we are owed. I think what where I'm running into issues is that while I did standardized as much as I could, there are still several columns for each study that don't apply to other studies. I.e. some studies have different arms they could be enrolled in, some are just a 1 time enrollment payment, others have several milestones that can receive payments. But every sheet has roll ups that are standardized that I need in the Pivot Table. Those being: Protocol Randomized Date Federal Accrued Foundation Accrued Industry Accrued Supplement Accrued Federal Received Foundation Received Industry Received Supplement Received Total Owed The Accrued and received columns sum the individual payments into those buckets, that way we can go back to the funder and ask specifically what we are missing for to see if they missed paying us for that milestone specifically. When I tried pulling all these sheets into Power Query, I was able too, and aggregated all the sheets into one via Power Query. Then I tried to pull that aggregate into a pivot table. No Pivot table Loaded and all I got was "load to data model failed" on each queries. Am I asking for too much? Can I get rid of the extra columns in Power Query that do no align together with ruining the data that is being pulled in by formulas. I have if statements pulling into the table for the individual, study specific milestones, from a separate table that automatically helps us track payments accrued, and the "standard columns" have sums formulas that sum the columns that apply to them from the individual milestone columns. The milestone, study specific received columns are entered in manually and have no formulas, but are rolled up into the standard columns just like in the accrued side. And the total owed column is also a formula of the standard accrued and received columns. The goal of pulling this into a pivot table is so we can give high level data to leadership to actually start tracking how much we are owed, given the constant delay in payments, and to have a real sense of the deficit this specific program runs year to year. This way they can accurately plan for the yearly "donation" from other sources of funding in the department. If you made it through this post, thank you! Any help is appreciated. I'm using Excel 365. submitted by /u/Melodic-Pollution-91 [link] [comments]
Tagged with
#real-time data collaboration#real-time collaboration#Excel alternatives for data analysis#financial modeling with spreadsheets#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#Excel alternatives#rows.com#digital transformation in spreadsheet software#intelligent data visualization#Time Tracking#Power Automate#Excel#Automate Process#Excel 365#Reporting#Pivot Table#ERP Software#Final Report