• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

EXCEL PIVOT TABLE QUESTION- for calculating previous value for specific time ranges|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / EXCEL PIVOT TABLE QUESTION- for calculating previous value for specific time ranges|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…EXCEL PIVOT TABLE QUESTION- for cal…
sp_PrintTopic sp_TopicIcon
EXCEL PIVOT TABLE QUESTION- for calculating previous value for specific time ranges
Page: 12Jump to page
Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
1
December 3, 2020 - 9:18 am
sp_Permalink sp_Print

Hello all,

My first post, hopefully, I can explain myself clearly as I am a novice in using excel.

I am trying to find out how to set a pivot table/create formula, whichever way is easier, to calculate value difference prior the specific time and to calculate total time within time range where no activity has been determined. 

I have added excel file which hopefully explains it, so I am looking for 4 value calculations, explained on the right side. I have done the first figures for the 01/10/20, and highlighted them in yellow as well on the data rows, just to show where I found the data. Obviously, I need to learn how to do this more efficient, rather than looking for the calculations manually.

 

Any help will be highly appreciated.

 

Many Thanks

 

Kaspars

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
December 3, 2020 - 10:16 am
sp_Permalink sp_Print

Hi Kaspars,

Welcome to our forum! Please try uploading your file again. You need to click the 'Start Upload' button after selecting your file.

Thanks,

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
3
December 3, 2020 - 10:18 pm
sp_Permalink sp_Print

Sorry, the initial file was exceeding the limit.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
December 5, 2020 - 7:53 am
sp_Permalink sp_Print sp_EditHistory

Hi Kaspars,

Do you have Microsoft 365/Office 365? I need to know what version of Excel you have so I know what functions you have available.

Also, do you want these results for each date in the dataset?

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
5
December 5, 2020 - 6:03 pm
sp_Permalink sp_Print

Yes, it would be 365.  Yes- please it would be for each date. If it makes things easier the time could be formatted in the numeric value.

 

Thank you 🙂

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
December 6, 2020 - 7:03 am
sp_Permalink sp_Print

Hi Kaspars,

Thanks for clarifying. Your first example, "Minutes past target confirmation time of 14:10" has 2:38 PM as the earliest confirmation time after 2:10 PM, however on row 6631, 6632 and 6633 there is a time of 2:34:39 PM. 

Did you simply overlook this earlier time, or is there something else I need to take into account, as the data isn't sorted.

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
7
December 6, 2020 - 8:04 pm
sp_Permalink sp_Print

Sorry Mynda, my mistake. Data wasn`t sorted correctly. I have changed it now. It should be sorted as basic as the earliest data and earliest time.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
December 7, 2020 - 8:35 am
sp_Permalink sp_Print

Hi Kaspars,

Thanks for clarifying. Please see file attached which uses the new dynamic array functions: 

XLOOKUP Function

FILTER Function

Kind regards,

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
9
December 8, 2020 - 9:40 am
sp_Permalink sp_Print

Thank you Mynda, that is excellent!!! And it is another reminder that I need to learn a lot about Excel. 

If it is possible, could you please adjust first time slot as - 1st Gap after 14:00, instead of 14:10. If it is time-consuming please leave it 🙂 I tried to do it today, but no luck- only more questions in my mind 😀

Also, I have attempted (again unsuccessfully) to add more data to the sheet which consists of your formulas, as I have another 104 users to add and calculate. Thought that copying/pasting additional data and dragging down the formula would work- but it didn't 🙁 Which is again my lack of knowledge so far. 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
December 8, 2020 - 12:05 pm
sp_Permalink sp_Print

Hi Kaspars,

You can change the formula in cell H3 to this:

=XLOOKUP(G3#+0.583333333333333,Table1[Date Time],Table1[Confirmation Time],"On Time",1)-0.583333333333333

 

0.583333333333333 is the time serial number for 14:00. If you enter 14:00 in a cell and then format it as general, you'll get 0.583333333333333.

More on working with dates and time in Excel here.

The source data in columns A:E is formatted in an Excel Table. All you need to do is paste your new data on the very next blank row at the bottom of the table and the ranges will expand to incorporate the new data in the Table's range. This will in turn automatically feed through to the formulas. As more dates are added, you'll need to copy the formulas in columns I:K down.

Note: the formulas don't differentiate between different users' data. If you want separate results for the different users then let me know and provide sample data containing multiple users.

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
11
December 9, 2020 - 9:56 am
sp_Permalink sp_Print

Thank you, another set of new information for me to learn 🙂 I have attempted numerous times to adjust the formula, but no success. I believe my MO Excel doesn't support XLOOKUP 🙁 I might be wrong, but I have that assumption. I will add screenshots.

*Understand now the point about pasting new data and adjusting formula range.

*If we add more data with multiple users, would I be able to select a filter on - Confirmed by and then see the calculation?

Preferably I would calculate these measures every 3 months, so trying to create it dynamically, so I can add the data, do the calculation and after 3 months remove existing data and update with a new one.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
12
December 9, 2020 - 12:58 pm
sp_Permalink sp_Print

Hi Kaspars,

You need to change your update channel from semi-annual to monthly to get the XLOOKUP function. Speak to your IT people to make that happen.

If you want to filter the data by the user, you'd need to modify the formulas to include a filter for the selected user ID. See attached, where you can choose the user from the drop down list in cell G2. There's only one user in the list at the moment, but when you update the data, it will automatically include new user IDs.

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
13
December 9, 2020 - 10:42 pm
sp_Permalink sp_Print

Thank you, Mynda, much appreciated 🙂

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
14
December 15, 2020 - 9:24 pm
sp_Permalink sp_Print

HI Mynda, sorry to ask again, but I still can`t get the results. I must be doing something wrong..  I will add the recent file which you have created and new data for the user2. 

 

Steps that I am taking:

1. Copying columns A & B from file  user2 data

2. Pasting them into columns A & B within file kaspars_time_calcs2

3. It updates the formulas successfully in Columns C & D

4. Copying column C from file  user2 data

5. Pasting them into column C within file kaspars_time_calcs2

6. This adds another user in the filter G2

So far so good 🙂

The problem is that it doesn't update the data into G: K Columns

What am I missing, please?

 

Many Thanks

 

Kaspars

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
15
December 16, 2020 - 8:51 am
sp_Permalink sp_Print

Hi Kaspars,

It works fine for me. Did you get your version of Excel updated to include the dynamic array functions?

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
16
December 16, 2020 - 10:31 pm
sp_Permalink sp_Print

I wasn`t able to update my works PC for up to date Excel version. But I have been using Excel subscription on my personal PC through my UNI`s account. At least it did recognize XLOOKUP functions. But it looks like it still missing something. I have added a screenshot with the current Excel version which I am using. It is strange that it seems like it does update only one cell H20, when I filter for USER2. When you update the data, are you then selecting USER2 through G2 filter and it updates all the times on the right side? 

Hmm.. It seems like I might need to purchase a current, up to date Excel version? If that is the case can you please advise which version I should go ahead with?

Many Thanks

 

Kaspars

 

Annotation-2020-12-16-122455.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Annotation-2020-12-16-122455.png (8 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
17
December 17, 2020 - 11:26 pm
sp_Permalink sp_Print

Hi Kaspars,

That version you have on your Uni PC should work. Please send me the file you're using that you've added the user 2 data to so I can test on my PC.

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
18
December 18, 2020 - 9:35 pm
sp_Permalink sp_Print

Hi Mynda,

 

Added as requested- 1.Initial Template with user1, 2. Data from 2nd user 3. My outcome when I add data from user 2 to Template 1

 

Many Thanks

 

Kaspars

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4438
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
19
December 19, 2020 - 7:43 am
sp_Permalink sp_Print

Hi Kaspars,

It appears to work fine for me. However, there's only one value that's different and that's on October 26 for the Minutes past target confirmation time of 14:00.

Is it that you're expecting different values for the different users and that's why you don't think it's working?

Mynda

Avatar
Kaspars Jansons
Member
Members

Excel Expert
Level 0
Forum Posts: 11
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
20
December 20, 2020 - 12:48 am
sp_Permalink sp_Print

Hi Mynda, yes you are correct- I was expecting the values to change. I did start everything from the beginning and added another users data and it worked correctly! 🙂 All the values updated and user filters worked as well. Sorry for taking this long, and thank you for all the help and patience 🙂

Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Tristram Sexton, Ben Hughes
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27182

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.