Function use question

[rjc4]

Hi All,,
In D1, I have the Function =Today() which I have formatted as Custom "ddmm" which converts the date to 0802 corresponding to the eighth of February.

I wish to use the result (0802) in a macro as a number.

Does anyone know how to do this please?

Cheers
RC

 

[macleanb]

what sort of number? I know it sounds strange - but do you want eight hundred and two, or do you want a number that equates to the eighth of february (what year)

 

[rjc4]

Hi Ben,

I want a number that equates to the day and month in four digit format which can be extracted from the =today() function. The year is of no consequence as the today function is well...today, and feeds from the computer date of the day in question, if that sounds sensible.

So I'm after 1403 if today is the fourteenth of march etc.

I've tried just copying the date to another cell as a value but it reconverts to a date number.

Come back if I'm not clear.
Thanks,
RC

 

[macleanb]

I am afraid I am still not clear. Please bear in mind that you are FORMATTING - not CONVERTING here, and "inside" the cell there is still a Julian number representing the date. You can really convert by using the "text" function in a cell, or you could use the "format" function in VBA. If you need more clarification just let us know.

 

[fairwinds]

Hi,

=TEXT(TODAY(),"DDMM")

or

=--TEXT(TODAY(),"DDMM")

 

[Norie]

Why not use the Now and Format VBA functions?

MsgBox Format(Now(), "ddmm")

 

[rjc4]

Hi Ben,

Sorry if confusing you. Let me explain. I'm using the code as below to access some web data. A requirement is to stipulate the day and month in the syntax of the code. The day and month always represents todays date.

I manually enter this information in cell B1 and my code picks this up and gets my data. What I'm trying to do is have that data i.e. todays date converted to day and month automatically, so that I don't need to manually enter it every time in B1.

Code:
Dim Entry_Web_Site As String
Dim YYY, Num
YYY = [A1]: Num = Format([B1], "0000")

Entry_Web_Site = "http://www.mydatasite.com/" & YYY & Num & ".htm"

So if I have in A1=Dat and B1=0802, I get data from the site of /Dat0802.htm. This means todays data. Tomorrow it will be Dat0902 if I enter 0902 in B1. But I want the B1 cell to get the 0802 or tomorrow 0902 for me without having to enter the data manually. It would be ideal to convert in the code but if it can be done in the cell, then that would be ok too. Hope this is a bit clearer. Maybe I'm after something that is not possible.

Cheers,
RC

 

[macleanb]

Hi

Norries:


Format(Now(), "ddmm")


is what you want - so


Entry_Web_Site = "http://www.mydatasite.com/" & Format(Date, "ddmm") & ".htm"


You may note that I have changed Now() to just "Date" these, in this context are equivalent (now will return a real number with the floating part being the time) I also default to the now() form usually (being an old VB hack) but I am trying to make my code look simpler - and decided to "upgrade" my choice - it really doesnt matter...

 

[Norie]

RC

How about this?

Dim Entry_Web_Site As String 
Dim YYY, Num 
YYY = [A1]: Num = Format([B1], "ddmm") 

Entry_Web_Site = "http://www.mydatasite.com/" & YYY & Num & ".htm"

or

Dim Entry_Web_Site As String 
Dim YYY, Num 
YYY = [A1]: Num = Format(Now(), "ddmm") 

Entry_Web_Site = "http://www.mydatasite.com/" & YYY & Num & ".htm"

 

[rjc4]

Hello everyone,

All those solutions work fine. Sorry I didn't explain clearly enough.

Thanks a lot.
Cheers,
RC