• 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

VBA to sum up total value of loop cycle time|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / VBA to sum up total value of loop cycle time|VBA & Macros|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 ForumVBA & MacrosVBA to sum up total value of loop c…
sp_PrintTopic sp_TopicIcon
VBA to sum up total value of loop cycle time
Avatar
shila din

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 23, 2018
sp_UserOfflineSmall Offline
1
November 23, 2018 - 6:06 pm
sp_Permalink sp_Print sp_EditHistory

Hi,

I'm a beginner in VBA. I've tried google anywhere and never found the way to do this. I've a request report contain multiple date&time depending on how many times requestor send back to us. I can count how many times requestor loop back the request at column B.

Example first request having 3 times of loop at column B,

I need to take value of date&time at each of these Header name and sum up= [Date(3)&Time(3) -Date(2)&Time(2)] + [Date(2)&Time(2) -Date(1)&Time(1)]  + [Date(1)&Time(1) -Date(0)&Time(0)] 

or can start at columnAB&AC and jump every 3 cells and the loop continue until it found empty cell.

It would be best if I can exclude weekend and Public Holiday.

can it be done?

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1550
Member Since:
October 5, 2010
sp_UserOnlineSmall Online
2
November 25, 2018 - 4:22 pm
sp_Permalink sp_Print

Hi Shila,

I'm not really clear on what your objective is.

The values you have in the columns headed Date and Time aren't dates or times, they are text, so doing any calculations with them is difficult.

You should store these values correctly, please see these

https://www.myonlinetraininghu.....e-in-excel

https://www.myonlinetraininghu.....e-and-time

Once you have the data stored correctly, what are you then trying to do with these dates and times?  Work out the number of days/hours between them?

It's often better to state what you want as a result rather than say what calculations you think will give you that result.  That way we can think of our own solution to the problem.

Regards

Phil

Avatar
shila din

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 23, 2018
sp_UserOfflineSmall Offline
3
November 25, 2018 - 5:51 pm
sp_Permalink sp_Print

Hi Phil,

I want to sum up at column C the total number of cycle time between multiple loop of date&time.

I put the formula in attachment as example of what 1 want.

Two of way I can think of:

1. start pick up Date&time from column AB&AC. Next date&time is jump every 5 cells. Loop using 'do until' cell is blank. Total up the differences every 2 date&time

2. using 'loop'. I've found the counter of loop at column B. start pick up Date&time from column AB&AC. Next date&time is jump every 5 cells until loop counter met. Total up the differences every 2 date&time

Pls advise if you have better way.

 

Thanks in advance

 

Regards,

Shila

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1550
Member Since:
October 5, 2010
sp_UserOnlineSmall Online
4
November 25, 2018 - 9:20 pm
sp_Permalink sp_Print

Your dates and times are still text.

If you actually make them dates/times you'll be able to do your calculations.

Regards

Phil

Avatar
shila din

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 23, 2018
sp_UserOfflineSmall Offline
5
November 26, 2018 - 6:34 pm
sp_Permalink sp_Print

Hi Philip,

I can view it as number instead of text because my computer format is 01.0.12018 instead of 01/01/2018.

Date-format.JPGImage Enlarger

Btw I've made a code but still have error. Not sure this is correct or not.

 

'extract formula down to last row of column C
Dim LastRow As Long, CurRow As Long
LastRow = Range("A" & Rows.count).End(xlUp).Row
'--------------------------------------------------
'looping of cycle time
Dim i As Long
Dim counter As Integer
Dim total As Double
Dim sumTotal As Double
Dim value1 As Date
Dim value2 As Date
value1 = activecell.Value + activecell.Value.Offset(0, 1).Value
value2 = activecell.Value.Offset(0, 5).Value + activecell.Value.Offset(0, 1).Value     'shift right 5 column from activeCell
counter = Range("B8").Value 'define loop#

Range("AB8").Select 'go to first cell

For i = 1 To counter 'start loop at 1 until defined as each cell at columnB

total = -(value1 - value2) 

Range("C8").Select = sumTotal    'place cell to put total. every loop will place down next row
sumTotal = sumTotal + total   'sum up all total
Next i

'-------------------------------------------------
For CurRow = 8 To LastRow
    'Range("C" & CurRow).Value = sumTotal

Next CurRow

sp_PlupAttachments Attachments
  • sp_PlupImage Date-format.JPG (66 KB)
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1550
Member Since:
October 5, 2010
sp_UserOnlineSmall Online
6
November 28, 2018 - 12:54 pm
sp_Permalink sp_Print sp_EditHistory

Hi Shila,

The data in the Date(x) and Time(x) columns are text.  Your date format in the regional settings doesn't mean you can use text as dates.

The data in the Created On column is a date.  If you select one of these you will see that in the formula bar it shows e.g. 14/10/2018 10:39:21 AM whereas if I select something in the Date(1) column all I see is 15.10.2018

Another way to check this is if you select multiple cells e.g. AG8:AG11, the status bar will just show you a count of 4 in the bottom right of your screen.

If these cells contained dates, you'd also see an Average and Sum, which is what you see if you select G8:G11.

Regards

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Alan Sidman, QSolutions Group
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
Hans Hallebeek: 185
Newest Members:
Kate Dyka
Kwaje Alfred Mogga
thong nguyen
Appiagyei Kofi Frimpong
Hilary Burchfield
Richie Wright
Adel Kock
Barbara Murray
Doug Milne
Siobhan Stringer
Forum Stats:
Groups: 3
Forums: 24
Topics: 6547
Posts: 28656

 

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

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • 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

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

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

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.