Solved

Macro to format data for date/time calculation and also calculate duration

Posted on 2014-10-10
8
211 Views
Last Modified: 2014-10-12
Data exists in 2 format:
1a. date
1b. Time
both in separate columns ( see worksheet 1 column A and B)

2. date and time together (see worksheet 1 column C)

I need to calculate the duration between the 2 data for the whole column - see column D and E.

My problem is that it is more accurate to work with the calculation if the data format is in date and time together, ie. no 2. instead of 1a and 1b. However, I have data format like 1a and 1b sometimes.

Thus I need:
1 macro to be able to combine 1a and 1b and then do an interval calculation; and if 2. exists, then use 2. for interval calculation

-"Date" will always be at column A
-"Time" will always be at column B
-"Date.Time" will always be at column C
-if either data is not available, it will be blank. For example, data in Column A and B exists but not Column C, then Column C will be blank. OR Data in Column C exists but not in Column A and B, then Column A and B will be blank.

MacroDateTime.xlsx
0
Comment
Question by:ceneiqe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40374024
do you want the solution by macro or by formula?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40374073
You should be able to do this without a macro.

First, the correct delta calculation when Dates are in column A and times are in column B is simple:
=(A3+B3)-(A2+B2)
(by the way, that corrects the erroneous result in cell E9).

Now, the issue really is this:  Do you really see a combination of the following types of data in two adjacent columns?
 date time example?
Because, that's just bad. :-)  But you could write a formula to handle this scenario:
=IF(B3="",A3,A3+B3)-IF(B2="",A2,A2+B2)

Your example workbook shows data in ALL three columns, and not like you described in the original post.  Clarify how the data is presented and we'll be glad to resolve this.

-Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40374092
Okay, I re-read your post.  If you data looks more like this:
date time date.time exampleThen insert this formula in row 3 and copy down:
=IF(A3="",C3,A3+B3)-IF(A2="",C2,A2+B2)

Example file attached.

-Glenn
EE-MacroDateTime.xlsx
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:ceneiqe
ID: 40375351
do you want the solution by macro or by formula?

a macro.

Thanks Glenn for the formula. I have attached my example. You are right in your comments in ID: 40374092.
please see column K for the formula you have proposed.

Is it possible to have a macro to insert the formula in column K ? (pls ignore the error in k408 to k597 -  i will fix the format later)
thanks.

EE-MacroDateTime--Example-.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40375423
It appears that your data population is not quite as I surmised.  You either have:
A) A Date in column A and a Time in column B
B) A Date & Time in column A (formatted as a short date), and the exact same Date & Time in column B (formatted as hh:mm:ss)
C) A Date in column A, a Time in column B, and the same Date & Time in column C (formatted as date + time)

The formula to do the durations in this case very similar (this in cell K3):
=IF(B3=A3,A3,A3+B3)-IF(B2=A2,A2,A2+B2)

If you want a macro to insert this formula, it might as well include a subroutine to clean up any time values in column B (i.e., those with leading spaces).
Option Explicit
Sub Insert_Duration()
    Dim rng As Range
    Dim cl As Object
    
    Range("K1").Select
    With Selection
        .Value = "Duration"
        .Font.Bold = True
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
        End With
    End With
    Columns("K:K").ColumnWidth = 10
    
    Set rng = Range("A3", Range("A3").End(xlDown))
    For Each cl In rng
        cl.Offset(0, 1).Value = Trim(cl.Offset(0, 1).Value)
        cl.Offset(0, 10).FormulaR1C1 = "=IF(RC[-9]=RC[-10],RC[-10],RC[-10]+RC[-9])" & _
                                       "-IF(R[-1]C[-9]=R[-1]C[-10],R[-1]C[-10],R[-1]C[-10]+R[-1]C[-9])"
    Next cl
    
    Set rng = Range("B2", Range("B2").End(xlDown))
    rng.NumberFormat = "h:mm:ss"
    Set rng = Range("K3", Range("K3").End(xlDown))
    rng.NumberFormat = "h:mm:ss"
    Range("A1").Select

End Sub

Open in new window


Example file attached.  Click on the "Desired Results" sheet (duplicate of Original Data) and run "Insert_Duration" macro.

-Glenn
EE-MacroDateTime-Example.xlsm
0
 

Author Comment

by:ceneiqe
ID: 40376258
Thanks. But when i use the code in my worksheet, it stopped at row 336.

EE-MacroDateTime--Example-v1-.xlsm

Please advise.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40376278
That's because your example data shown in your previous attachment (EE-MacroDateTime--Example-.xlsx) is not the same as the data you're now showing in your latest post.  In the first example, if there was a value in column C, it was a duplicate of values in column A&B combined.  Now you're showing a data set where there is no data in A&B if there is data in C.  This is like the example I posted up in #40374092 above.

This revised code/workbook will work for the data set you just presented.  I've updated the data for you already.

-Glenn
EE-MacroDateTime--Example-v1.xlsm
0
 

Author Closing Comment

by:ceneiqe
ID: 40376360
Very fast!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question