Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding Time

Posted on 2015-02-22
11
Medium Priority
?
76 Views
Last Modified: 2015-02-22
EE Pros,

I have two columns.  In one column, I want to put a time in the first cell below a Column Name (Actual Time).....so 8:00am. Then the column next to it, I want to be able to put a time AMOUNT in (such as .30 for 30 minutes or .90 for 90 minutes ..... and so on.  As I put in the time, I need a macro that sees the additional add of time and produces a new time based on the add.   So in my example above, if I start with 8:00am in col. F6 and in column g6 out in .30, the new time in col. F7 will be 8:30am.  

Hopefully this is not too much of a challenge.

Thank you in advance,

B.
Time-and-timing.xls
0
Comment
Question by:Bright01
[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
  • 6
  • 3
  • 2
11 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624183
Hi

Is that what you are looking for?

Just need to add the timing in minutes in column G and the actual time will populate in column F
Time-and-timing-update.xls
0
 
LVL 34

Assisted Solution

by:Norie
Norie earned 400 total points
ID: 40624196
Do you definitely want the new time in the cell below the original time?

Wouldn't it be better to put it in column H on the same row as the time and increment?

That's what this worksheet event code will do.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        With Target.Offset(, 1)
            .Value = Target.Offset(, -1).Value + Target.Value
            .NumberFormat = "[h]:mm"
        End With
        Application.EnableEvents = True
    End If
End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624200
If you want the time in column F to adjust as you inter minutes in G, you can use this file.
Time-and-timing-update.xls
0
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!

 

Author Comment

by:Bright01
ID: 40624203
Neither of these work exactly as I need.  In the first case, I need a way of adding incremental time in a normal format such as 10 min., 30 min........ like .10 or 10 or .3 or 30.  The construct is correct, the way it's entered is not.

As for putting a row view in,.... good idea, but couldn't get it to work with the Macro.

Thanks both for jumping in on this.... hopefully not too complicated.

b.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624211
please have a look at this. Hope this is what you are looking for.
Time-and-timing-update.xls
0
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 1600 total points
ID: 40624220
And in this attachment, just enter the number of minute like: 30 and you will see in the cell 30 min.
Time-and-timing-update.xls
0
 

Author Comment

by:Bright01
ID: 40624227
Jean-Marc,

Still, if I want to add 30 minutes, unless I'm reading your Sheet wrong, I have to put in 12:30:00 AM.  I need to be able to put in 30 minutes in some simple format....such as .3 or 30.

Thanks,

B.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624232
are you adding minutes (Column G) to time in Column F or your are counting minutes between to times in Column F?
In my sheet, the time will adjust based on the minutes entered in column G
0
 

Author Closing Comment

by:Bright01
ID: 40624236
Great job!

Works well.  Look for the next version request on this later today.

Thanks again,

B.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624237
I'm glad i was able to help. :-)
0
 
LVL 34

Expert Comment

by:Norie
ID: 40624266
If you just want to enter the minutes as 30, 60, 90 etc alter the code I posted to this.
[code]Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        With Target.Offset(, 1)
            .Value = Target.Offset(, -1).Value + TimeSerial(0,Target.Value,0)
            .NumberFormat = "[h]:mm"
        End With
        Application.EnableEvents = True
    End If
End Sub

Open in new window

0

Featured Post

Technology Partners: 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 a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

722 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