Solved

Adding Time

Posted on 2015-02-22
11
64 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
  • 6
  • 3
  • 2
11 Comments
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
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 33

Assisted Solution

by:Norie
Norie earned 100 total points
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Bright01
Comment Utility
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
Comment Utility
please have a look at this. Hope this is what you are looking for.
Time-and-timing-update.xls
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Accepted Solution

by:
Wilder1626 earned 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Great job!

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

Thanks again,

B.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
I'm glad i was able to help. :-)
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now