VBA Code based on NOW (time)

[poikl]

Hi,
I have the following code in MACRO in ColM to enter initials based on the NOW entry in ColA. However it always enters FM no matter what time so it's not working!
In ColM I have entered "Cells(c.Row, "m").Formula = "=IF(RC[-12]<=TIMEVALUE(""3:50:00 PM""),""RB"",""FM"")"
In ColA I have:"Cells(c.Row, "A").Value = Format(Date, "m/d/yy") & " " & Format(Time, "hh:mm:ss")
Can you please help correct the code so that an entry after 3:50PM shows "FM" and anything earlier (of same date) shows "RB"?

 

[MARK858]

If I can only ask if you would please be able to include in the above Code another possibility to enter in ColM RB (besides the one above) that if entry in ColA equals the one directly above or below it would ALSO enter RB in ColA.
So for example if the Time entered in Row 10 ColA either equals Row 9 or Row11 it would show RB in ColM (even if it's past 3:50 PM)

I'll look at it tomorrow

 

[poikl]

Thank you so much in advance, and will be looking forward:
Just to clarify: If you can please include in the code that you've graciously written for me, "Cells(c.Row, "m").FormulaR1C1 = "=IF(INT(RC[-12])=TODAY(),IF(RC[-12]<=TODAY()+TIMEVALUE(""3:50:00 PM""),""RB"",""FM""),"""")"
an OR and AND statement that if entry in ColA equals the one directly above or below it would ALSO enter RB in ColA.

 

[MARK858]

another possibility to enter in ColM RB (besides the one above) 
that if entry in ColA equals the one directly 
above or below it would ALSO enter RB in ColA.

the one directly above or below it would ALSO enter RB in ColA.

that if entry in ColA equals the one directly above or below it would ALSO enter RB in ColA.

if the Time entered in Row 10 ColA either equals Row 9 or Row11 it would show RB in ColM

The bolded statements above do not match.... please clarify

Please also note...
If it is ColA
Then it would need to be VBA not a formula because It would cause a circular reference.
You can't have a value and a formula in the same cell
It will affect the formula in ColM

 

[poikl]

I’m very sorry Mark for the typo and thank you so much for noticing and replying so quickly. Yes I was asking for help to adjust an existing VBA MACRO.
But wherever it says “it would ALSO enter RB in ColA.” I actually meant ColM. You already had written for me that it should enter RB in ColM if it’s after 3:50 so I’m asking if you can include in that same VBA command an additional possibility for RB to show in ColM if the Time shown in ColA either equals the row above or the Row below. Again I’m sorry to have confused you with my mistake but it has been a very tiring night yesterday

 

[MARK858]

Try (assumed that you only want the last test to apply only if it is todays date)...

Cells(c.Row, "m").FormulaR1C1 = _
        "=IF(AND(INT(RC[-12])=R3C26,OR(RC[-12]=R[-1]C[-12],RC[-12]=R[1]C[-12])),""RB"",IF(INT(RC[-12])=R3C26,IF(RC[-12]<=R3C26+TIMEVALUE(""3:50:00 PM""),""RB"",""FM""),""""))"

Book1
	A	B										L	M												Y	Z
1	07/11/2024 22:17
2	08/11/2024 22:17												RB
3	08/11/2024 22:17																									08/11/2024
Sheet5

Cell Formulas
Range	Formula
M2	M2	=IF(AND(INT(A2)=$Z$3,OR(A2=A1,A2=A3)),"RB",IF(INT(A2)=$Z$3,IF(A2<=$Z$3+TIMEVALUE("3:50:00 PM"),"RB","FM"),""))
Z3	Z3	=TODAY()

Book1
	A	B										L	M												Y	Z
1	07/11/2024 22:17
2	08/11/2024 22:17												FM
3	07/11/2024 22:17																									08/11/2024
Sheet5

Cell Formulas
Range	Formula
M2	M2	=IF(AND(INT(A2)=$Z$3,OR(A2=A1,A2=A3)),"RB",IF(INT(A2)=$Z$3,IF(A2<=$Z$3+TIMEVALUE("3:50:00 PM"),"RB","FM"),""))
Z3	Z3	=TODAY()

Book1
	A	B										L	M												Y	Z
1	08/11/2024 22:17
2	08/11/2024 22:17												RB
3	07/11/2024 22:17																									08/11/2024
Sheet5

Cell Formulas
Range	Formula
M2	M2	=IF(AND(INT(A2)=$Z$3,OR(A2=A1,A2=A3)),"RB",IF(INT(A2)=$Z$3,IF(A2<=$Z$3+TIMEVALUE("3:50:00 PM"),"RB","FM"),""))
Z3	Z3	=TODAY()

 

[poikl]

Thank you again for all your help it must’ve been so time consuming.
I had to leave work early so I won’t be able to try it till I get back early next week.
Just may I ask it seems that you created a Formula for me to copy and paste down the row but I was hoping if it was possible to include the added criteria (of ColA date & time equals row on top or below) into your previously created VBA code in my existing MACRO?

 

[MARK858]

It is the same macro, the tables are the results

 

[poikl]

Thank you. I can’t wait to try it out and will let you know how it goes

 

[poikl]

Hi
I copied and pasted the VBA code you wrote but ColM is BLANK!
Was I supposed to also enter the Formulas you wrote on the Excel Sheet "
=IF(AND(INT(A2)=$Z$3,OR(A2=A1,A2=A3)),"RB",IF(INT(A2)=$Z$3,IF(A2<=$Z$3+TIMEVALUE("3:50:00 PM"),"RB","FM"),""))
Z3 Z3 =TODAY()"?

 

[MARK858]

Was I supposed to also enter the Formulas you wrote on the Excel Sheet "

No!

When I run

Sub testFormula()
    Dim c As Range
    For Each c In Range("A2:A10")

        Cells(c.Row, "m").FormulaR1C1 = _
                                      "=IF(AND(INT(RC[-12])=R3C26,OR(RC[-12]=R[-1]C[-12],RC[-12]=R[1]C[-12])),""RB"",IF(INT(RC[-12])=R3C26,IF(RC[-12]<=R3C26+TIMEVALUE(""3:50:00 PM""),""RB"",""FM""),""""))"
    Next

End Sub

With the data as per the below

Book1 (version 1) new.xlsb
	A	B										L	M											X	Y	Z
2	10/11/2024 02:17:10
3	09/11/2024 22:17:16																									11/11/2024
4	11/11/2024 22:17:16
5	11/11/2024 22:17:16
6	11/11/2024 22:17:16
7	10/11/2024 22:17:16
8	11/11/2024 04:17:16
9	11/11/2024 12:17:16
10	11/11/2024 16:17:16
Sheet5

Cell Formulas
Range	Formula
Z3	Z3	=TODAY()

I get

Book1 (version 1) new.xlsb
	A	B										L	M											X	Y	Z
2	10/11/2024 02:17:10
3	09/11/2024 22:17:16																									11/11/2024
4	11/11/2024 22:17:16												RB
5	11/11/2024 22:17:16												RB
6	11/11/2024 22:17:16												RB
7	10/11/2024 22:17:16
8	11/11/2024 04:17:16												RB
9	11/11/2024 12:17:16												RB
10	11/11/2024 16:17:16												FM
Sheet5

Cell Formulas
Range	Formula
Z3	Z3	=TODAY()
M2:M10	M2	=IF(AND(INT(A2)=$Z$3,OR(A2=A1,A2=A3)),"RB",IF(INT(A2)=$Z$3,IF(A2<=$Z$3+TIMEVALUE("3:50:00 PM"),"RB","FM"),""))

Have you put =TODAY() in Z3?