Time formulas

Bdcrawford · #1 11-25-2016, 01:55 PM

I'm looking to have a formula that will enter a time in a cell based on a value being entered in another cell. As an example id like the time of day to be in cell H2 based on any text value entered in cell A2.

Thank you

macropod · #2 11-25-2016, 10:31 PM

You're going to have to provide a lot more detail than that. For example:
What text in A2
What times in H2 for the A2 values.

Depending on your requirements, a few IF tests might be suitable or a lookup table might be required. Or it might not even be practical...

Bdcrawford · #3 11-26-2016, 06:11 AM

Quote:

Depending on your requirements, a few IF tests might be suitable or a lookup table might be required. Or it might not even be practical...

I have a login sheet and everyone has a number they use to login for the day. This number varies and is both letters and numbers. I don't know what other information is needed to create the formula. I need a time of day in cell H2 based on any value in A2. This formula will need to be copied down several rows as there is up to a few hundred that sign in daily.

Does this information help?

jeffreybrown · #4 11-26-2016, 08:28 AM

It sounds as if when the user enters the logon in A2, you want H2 to capture the time the entry was made.

This can be done be formula, but it can't be made to be static unless you use VBA.

The formula in H2 copied down would be =IF(A2<>"",NOW(),"")

but you have to place a time format on the cell on you will just get numbers.

The NOW() function in the formula is Volatile means if you select anything on the spreadsheet the time will change...it's dynamic.

You can test this by selecting F9 and you'll see the time change.

So, bottom line, if you want to lock in the time when A2 is updated, you'll need VBA.

Do you want to go this route?

Bdcrawford · #5 11-26-2016, 10:31 AM

@jeffreybrown

If you remember the worksheet you helped me with before. This pertains to that same worksheet. As people enter we need to track them coming in so I'm looking for a way to minimize what all needs entered so the time of day will have to be locked.

jeffreybrown · #6 11-26-2016, 02:57 PM

No I don't specifically remember your other spreadsheet, but this should work as sheet module code...

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Parent.Range("A2:A100")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    If Target <> "" Then Target.Offset(, 7).Value = Time
    Target.NumberFormat = "h:mm AM/PM"
End Sub

http://www.rondebruin.nl/win/code.htm

Bdcrawford · #7 11-26-2016, 04:25 PM

@jefferybrown

I copied and pasted the code you provided in the VBA editor it doesn't insert a time into cell H2 when I enter a value into A2. Is there something else I need to do?

I have attached the workbook for this formula to make it easier. I would like to show the time into column H that each person entered the complex by the value that is entered in column B.

so as each person enters the gate we can show the time they entered by the pass# assigned to them. I can make the pass# whatever I want except for us the numbers and letters represent a location and guest number so we know their location in the complex.

Thank you,

jeffreybrown · #8 11-27-2016, 07:09 AM

With the attached file, it appears you mean column B! Column A already has a count in it.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Parent.Range("B2:B100")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    If Target <> "" Then
        With Target.Offset(, 6)
            .Value = Time
            .NumberFormat = "h:mm AM/PM"
        End With
    End If
End Sub

Bdcrawford · #9 11-27-2016, 10:22 AM

@jeffreybrown

I realized that I had specified the wrong location last night when at work but couldn't respond to let you know that.

I got the code to work but its changing the value entered into B2 to 12:00 am.

I need the value entered to stay the same.

any thoughts?

As I continue to mess with this method there are a few bugs in it. when I use a numerical number from the call-in log it changes that number to "12:00am" I can then format the column to "text or general" and it will change it back to the numerical value entered.

the other issue is that I need to protect this sheet so other users cant screw up the formulas and I'm getting an error when protecting the sheet. I tried unlocking the column H where time is being entered and there is still an error popping up even though it adds the time in column H

jeffreybrown · #10 11-27-2016, 12:16 PM

Try this workbook and let me know what works for you and what doesn't.

It is password protected with "aaa", but just on the first sheet.

Bdcrawford · #11 11-27-2016, 12:51 PM

@jefferybrown

that works great the way it is setup.

We use military time here though so the code changes back when entering the value in B2 so I assume the code time needs typed as military to achieve this?

one weird thing though is the first 3 cells in column H are italicized then the rest a different format.

thank you

jeffreybrown · #12 11-27-2016, 01:04 PM

Right, just update the time format in the code to...

Code:

.NumberFormat = "hhmm"

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Employee Turnover by Department/Month split between Voluntary/Involuntary - Full-time/Part-time	CPSmith	Excel	1	03-26-2016 02:48 AM
Time Stamps automatically changed to same time!	hydrogyny	Word	4	02-09-2015 08:57 PM
Time allotted minus time used with result in hour and minute	esther6086	Excel	2	04-29-2014 05:03 PM
Time & Date Formulas in Graphs	Ady_E	Excel	5	11-15-2012 05:29 AM
IE Object: Run-time problem (the link is not clicked in run-time but not in step-in	tinfanide	Excel Programming	1	03-04-2012 12:05 AM