VBA DATE on open with variable based on time

[Mshuell]

Good evening everyone I hope you can help me with this. I've searched high and low and have found my googling skills to be lacking for this.

I'm looking at having VBA give a date based on time from sheet1 to Blank Officer on workbook open. this is some of the coding I have put together but its not working.
if the workbook is opened from 00;00 to 03:00 it needs to have the previous days date. any other time it is opened the current date should be assigned.

on the very bottom I do have two macro buttons that do work, but i'm trying to get rid of those any help would be appreciated.


Private Sub Workbook_Open()
Dim shtSheet As Worksheet

With ThisWorkbook.Sheets("Sheet1")
With .Range("B15")
.Value = Now()

End With
End With
With ThisWorkbook.Sheets("Blank Officer")
With .Range("AB7")
.Value = Worksheets("Sheet1").Range("B20")


End With
End With



End Sub

Mr excel test .xlsm
	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
7					Day	=NOW()		Date		=NOW()
8
9
Blank Officer

Cell Formulas
Range	Formula
X7	X7	=IF(ISBLANK(AB7),"",TEXT(AB7, "ddd"))

Cells with Data Validation
Cell	Allow	Criteria
AA8:AA9	List	=Sheet1!$F$1:$F$9
AE8:AE9	List	=Sheet1!$F$1:$F$9
S7:V7	List	=Sheet1!$D$11:$D$16

Mr excel test .xlsm
	B	C
15	23:57
16
17	0:00	5:45
18
19	=NOW()
20	=NOW()
21
Sheet1

Cell Formulas
Range	Formula
B19	B19	=IF((AND(B15>=B17,B15<C17)),"=Date-1","=NOW()")
B20	B20	=B19

Private Sub CB1_Click()

Dim shtSheet As Worksheet


With ThisWorkbook.Sheets("Blank Officer")
With .Range("AB7")
.Value = Date
.NumberFormat = "YYMMDD"


End With
End With

End Sub
Private Sub CB3_Click()

Dim shtSheet As Worksheet


With ThisWorkbook.Sheets("Blank Officer")
With .Range("AB7")
.Value = Date - 1
.NumberFormat = "YYMMDD"


End With
End With



End Sub

 

[mart37]

You can use a IF-statement to control the time.
Something like: If Time >= "00:00:00" And Time <= "03:00:00" Then .....

 

[Mshuell]

You can use a IF-statement to control the time.
Something like: If Time >= "00:00:00" And Time <= "03:00:00" Then .....

try a few different variations but was never able to get it to work correctly. beginner or noob would better describe my knowledge base of VBA and in general coding in excel.

 

[Mshuell]

You can use a IF-statement to control the time.
Something like: If Time >= "00:00:00" And Time <= "03:00:00" Then .....

thank you Mart it got me to thinking and was able to solve it.

 

[Mshuell]

=IF((AND(A15>=A18,A19<=A15)),NOW()-1,NOW()) this is what i came up with in the cells but as i thought i had i figured it out murphy got me. this formula will work until the day actually changes then it fails to properly display. A15 is the now() time A18 is 00:00 andA19 is 03:00.

any additional information for what I'm doing wrong here would be greatly appreciated

 

[mart37]

AND(A15>=A18,A15<=A19)

 

[Mshuell]

Thank you Mart37 we have been running the code and found some issues on our end that wont work. can i get your assistance with transforming this code into VBA code for a button click.

Thank you sir