Convert date and time

What exactly is in Q4 and I29? Are they actual date/time serial numbers or are they text strings? Many of these questions usually end up being more about converting text to a date/time serial number that Excel (and VBA) can use, so many of these questions often start by needing to know exactly what is in the cell(s) in question.

You say that you want to "exploit the dates in a specific format". Exactly what do you mean by this? When stored as date/time serial numbers or as date type variables in VBA, the value does not really have a specific format. Normally one stores the date/time data as serial numbers, performs the needed calculations, then stores the result as a serial number and formats in the desired format. It might be important to understand exactly what you are trying to do and why format is important (whether the format is only important at the final output stage or if something about the format is essential to the processing steps).

I don't see a text() function in VBA, so I expect that statement would fail. What do you get from Scheduletime=worksheets("PLANNING").Range("Q4").value2? What do you get from =ISTEXT(Q4) entered in any convenient cell?

Assuming that Q4 and I29 contain date/time serial numbers, I would expect the procedure to look something like

Please Login or Register  to view this content.

Most problems with code like this will be when Q4 and I29 contain text strings and not numbers.

If it's a real date and a real time in Excel, then I would not convert to a string while importing to VBA. Excel and VBA tend to work with dates and times best when they are stored as unformatted numeric data types (double in this case) or date data types. Once converted to a string, dates become more difficult to work with.

Please Login or Register  to view this content.

I've changed the data types so that VBA should store the dates and times as doubles. The final result is converted to text at the end. I've also added a Stop statement so you can step through the code and see what each line is doing and take advantage of VBA's debugging tools.

But the date is retrieved in this format (mm.dd.yyyy) however I would like to retrieve the scheduleTime date in this format (mm/dd/yyyy) i.e. replace the (.) By the (/)

I don't understand this. Using a double data type, the date read in (the value in datein) should have no format -- just an integer. Likewise, the value of timein should have no format -- just a number between 0 and 1. VBA has no idea that these represent dates until you get to the "scheduletime=Format(...)" statement where the number is converted to text in the specified format. If you want "/" delimiting the parts of the date, it should be a simple matter of entering the appropriate number format code "mm/dd/yyyy h:mm".

As for the message sending part, I'm not sure I understand that part either. What is in P32 (post #12)? From what I can see, in post #12, the code assigns a value to Message when P32 contains something that can be interpreted as "False", and does nothing when P32 is "True". In post #13, the code will assign the specified text to Message when the time of execution is after 18:00. It isn't clear to me what you want to be different here.

actually datein is retrieved in number = 44120, and timein = 0.291666666667 however at scheduletime = 10.16.2020 7:00 I don't know if it's related to the date format of my OS.

What are you using in your Format() function when you assign dateout to scheduletime? It's possible that your OS's regional settings are impacting this, but I don't see the same problem on my computer. I can assign a date format that is different from my OS settings without difficulty. I'm not sure why your copy of Excel/VBA would do something different. Have you tried changing your OS settings?

As for the decision tree, for the post #12 case, it is testing if "rowdaterdv" (read in from Q4) is the same as "today" in P32. If it is always executing the "Else" branch, that means it is seeing that rowdaterdv and the value in P32 are not equal. Debugging this will require knowing exactly what is in rowdaterdv and exactly what is in P32 to determine if and why VBA is making an "incorrect" choice here.

For the post #13 case, it again will require debugging exactly what now-date is returning and exploring why that is different from expected.

Perhaps another observation. I have no experience sending text messages using VBA, but I don't see any code in what you have shared that seems to send the message. What you have shared assigns a value to the variable "Message" (or does not assign anything to message depending on the If condition), but I don't see anything that seems to actually send the message. Is it possible the, for this question, you need to be looking elsewhere for the code that actually sends the message and include the If condition there?

How to make that when a condition is not check the execution point directly on end sub

That sounds like you want an Exit Sub statement https://docs.microsoft.com/en-us/off...exit-statement Something like

Please Login or Register  to view this content.

Originally Posted by rodryg123

Hi,
So I tried a small macro to force the format change, but so far it doesn't work

Please Login or Register  to view this content.

I'm not sure exactly what you mean by "doesn't work". Usually if changing the number format does not change what you see in the cell, that means the date stored in the cell is a text string and not a real date/time serial number. This is usually fixed by correctly entering the data into the cell so it is a real date time serial number. Strategies for converting text to number/date (being aware of how you system's regional settings impact some of these strategies): https://support.microsoft.com/en-us/...rs=en-us&ad=us

As for the other issue, again, assuming the dates in column C are real dates/times, the test for "is today the same as the date in column C" should be something like [worksheet formula] TODAY()=C2 [VBA statement] If Date = worksheet.cells(2,3).value then do something

Nest something similar to that statement inside of a loop:

Please Login or Register  to view this content.