Change date Time Stamp to 8:00 AM based on critieria

[Huizar]

Hello All,

Is there away to change my time date time stamp to 8:00 AM. The reason im doing this is I'm trying to get the the amount of minutes it takes for an engineer to contact a customer.

For example Assigned time is 1/3/2019 7:45 AM, but i cannot use this cell for my calculation because worker doesn't start until 8:00 AM. And anything after 5:00 PM put it to the following 8:00 AM Time

So essentially i would want the cell to be 1/3/2019 8:00AM, but if the assigned time is anything after 8:00 AM, then just leave the cell with its regular time stamp.

The calculation i'm running is Assigned time - Contact Time = Minutes elapsed.

Hope this Makes Sense

 

[Joe4]

Here is a little sample script that will do the following:
- if the current time is before 8:00 AM, it will make the date/time stamp to be 8:00 AM the current day
- if the current time is after 5:00 PM, it will make the date/time stamp to be 8:00 AM the next day

You should be able to use the logic in your code:

Sub Test()

    Dim dtStamp As Date
    Dim dateOnly As Long
    Dim timeOnly As Double
    
'   Get current date/time
    dtStamp = Now()
'   Get date portion only
    dateOnly = Int(dtStamp)
'   Get time portion only
    timeOnly = dtStamp - dateOnly
    
'   If time is before 8:00 AM, make it 8:00 AM
    If timeOnly < (8 / 24) Then
        dtStamp = dateOnly + (8 / 24)
    Else
'       If time is after 5:00 PM, make it 8:00 AM the next morning
        If timeOnly > (17 / 24) Then
            dtStamp = dateOnly + 1 + (8 / 24)
        End If
    End If
    
'   Paste date stamp in cell A1
    Range("A1") = dtStamp
    
End Sub

 

[Gerald Higgins]

Hi, your request is a little vague, it's not clear how your time stamp is being calculated or stored.

But let's say it's in cell A1, here's one way.
Then in some other cell, let's say A2 . . .
=IF(MOD(A1,1)<8/24,INT(A1)+8/24,A1)

Then run your minutes elapsed calculation on THIS cell, not your original time stamp.

 

[Huizar]

Hi, your request is a little vague, it's not clear how your time stamp is being calculated or stored.

But let's say it's in cell A1, here's one way.
Then in some other cell, let's say A2 . . .
=IF(MOD(A1,1)<8/24,INT(A1)+8/24,A1)

Then run your minutes elapsed calculation on THIS cell, not your original time stamp.

Hi Gerald the formula that you provided was superhelpful. There were a few parameters that they changed on me and Iwas trying to integrate your formula you provided me but no luck. Wondering if you could assist.

I have be given start time for Individuals, like 7:30 AM, 8:30 AM, 11 AM,etc. Basically the formula you gave me provides me with a start time of 8o’clock if any time is assigned before 8AM.
Now I have to based it on individual start time ratherthan have a generic start time. I have alist.

For example:
Individual A: Start time is 11:00 AM
Individual B: Start time is 7:30 AM
So if the assigned time is 6:00 AM, then clock shouldstart for Individual A would be 11:00 AM. If it was assigned to B, then clockstart would be 7:30 AM. Unless the assignment is after their start time then leave assigned time as is.

The Output would basically look like: 1/2/2019 7:30 AM or1/2/2019 11:00 AM.
The initial formula you provide me =+IF(MOD(W2,1)<8/24,INT(W2)+8/24,W2)worked great with just a parameter of 8 AM. I tried adjusting it look up mymaster list but I changes my output a bit.

Your help would be super helpful.
Thank you