Time VBA label and inbetween

Lauren279

Board Regular
Joined
Aug 19, 2014
Messages
91
hi

I need help, I have a label that displays the date and time "dd-mmm-yyhh:mm ampm"
once the user form shows I have 2 images that I want it to show based on theresult of what the time is currently.

for example if the label says 07-Jul-18 06:00 am and the label is within 20minutes of the current time then image1 will show but anything outside of thistime image2 will show.

So far I have
If label1.Caption > Format(Now – 0.01, "dd-mmm-yyhh:mm ampm") Then
Image2.Visible= False
Image1.Visible= True
Else
Image2.Visible= True
Image1.Visible= False
End If
But that doesn’t seem to be working and I think I am justgetting confused with images/greater than/less than and the whole time factoris just not working for me.

Please HELP me.

 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
Hi

Sorry if this seems too obvious, but should there be a space in between the date and time?

dd-mmm-yyhh:mm ampm

dd-mmm-yy hh:mm ampm

So that this doesn't appear to Excel as a string of number characters.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
Hi,
untested but see if this update to your code does what you want


Code:
 Dim Date1 As Date, Date2 As Date
    Dim Diff As Long
    
    Date1 = CDate(Me.Label1.Caption)
    Date2 = Now - 0.01
    Diff = (Date1 - Date2) * 24 * 60
    
    Image1.Visible = CBool(Diff < 20)
    Image2.Visible = CBool(Diff > 20)

solution assumes that your label caption contains a valid date


Dave
 
Last edited:

Lauren279

Board Regular
Joined
Aug 19, 2014
Messages
91
worked perfectly, but I just had to change a few things
Image1.Visible = CBool(Diff < 0.002)
Image2.Visible = CBool(Diff > 0.002)

Date2 = Now - 0.007 (But that’s cause I decided on a shorter time frame)

Thank You!
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
Hi,
glad solution helped - many thanks for feedback

Dave
 

Forum statistics

Threads
1,148,291
Messages
5,745,897
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top