Problem with Date ... 5

[Rodie]

Hi all

I want to add "n" days to a date. So, I use the functions "Format()" and "DateAdd()" like that:

Dim final_date as Date
final_date = day & " " & month & " " & year  [COLOR=green]' 28 6 2004[/color]
final_date = Format(final_date, "General Date")
final_date = DateAdd("d", n, final_date)

The problem : on differents computers, "Format()" gives 28/6/2004 or 6/28/2004.

My program must work on all computers, so I don't where I can't fix "d m yyyy" in my code...

Thanks for your help.
Rodie

 

[PHV]

Have you tried this ?
final_date = DateAdd("d", n, DateSerial(theYear, theMonth, theDay))
MsgBox Format(final_date, "d m yyyy")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

Surely there is no need for that

n = 10
myNewDate = format(myOldDate + n,"dd/mm/yyyy")

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Rodie,

Real DATES are just NUMBERS, like today is 38176. The FORMAT is ONLY what you CHOOSE to display for CONVENIENCE in relation to your audience.

So adding n days to a date is merely

NewDate = MyDate + n

Display it any ol' way your heart desires! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Rodie]

Re hi ...

For you xlbo and SkipVought
The problem is that you don't know how is the stardard date for the computer... So, [red]Format(final_date, "...")[/red]can't work because you don't know if your computer understand "final_date" as dd mm yyyy, or mm dd yyyy ... For this reason, your code doesn't work... Sorry.

But PHV is right ! Congratulations... [red]DateSerial(year, month, day)[/red]
With this function, your date (day, month, and year) is clearly definited for the computer, and only after, you can use "Format()"

Dim final_date as string
final_date = DateAdd("d", n, DateSerial(year, month, day))
final_date = Format(final_date, "d m yyyy")    ' as you wish ...

Just a last thing, for you PHV: when "final_date" is declared as a Date, it doesn't work... snif ... but as a String, very good...

Thanks a lot guys for your quick answers.
Hope this will help...

Rody

 

[Rodie]

Re hi ...

For you xlbo and SkipVought
The problem is that you don't know how is the stardard date for the computer... So, [red]Format(final_date, "...")[/red]can't work because you don't know if your computer understand "final_date" as dd mm yyyy, or mm dd yyyy ... For this reason, your code doesn't work... Sorry.

But PHV is right ! Congratulations... [red]DateSerial(year, month, day)[/red]
With this function, your date (day, month, and year) is clearly definited for the computer, and only after, you can use "Format()"

Dim final_date as string
final_date = DateAdd("d", n, DateSerial(year, month, day))
final_date = Format(final_date, "d m yyyy")    ' as you wish ...

Just a last thing, for you PHV: when "final_date" is declared as a Date, it doesn't work... snif ... but as a String, very good...

Thanks a lot guys for your quick answers.
Hope this will help...
Anyway, I'm very impressed by your stats... you are in the TOP 4 of this site ! I wonder how much time you pass everyday to help the others... I hope you think about you too ... I frankly congratulate all 3 of you !!!

Rody

 

[SkipVought]

Rodie,

The PROBLEM is NOT as you stated...

The problem : on differents computers, "Format()" gives 28/6/2004 or 6/28/2004

YOUR problem was...

Given Year, Month and Day, how can I generate a DATE?

FORMAT has NOTHING to do with YOUR PROBLEM.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

Hmmm - the method that myself and Skip posted WILL work as long as a true date is passed to it

Dates are, in reality only serial numbers and as such, formatting is merely a visual tool

Test this
format cell A1 as "dd/mm/yyyy")
format cell A2 as "mm/dd/yyyy")
enter the same date in both cells (lets say 1st July 04 so

01/07/04 in A1 and
07/01/04 in A2

Put this code in a module:

Sub Test_Dates()
myDate1 = [A1]
myDate2 = [A2]
n = 10
myNewDate1 = format(myDate1 + n,"d m yyyy")
myNewDate2 = format(myDate2 + n,"d m yyyy")

msgbox "From UK Format : " & myNewDate1 & vbcrlf & "From US Format : " & myNewDate2
end sub

You will notice that you get the same answer for both - therefore proving that the format of a date is unimportant until you decide to display it

I believe that it isnot working for your situation though as you are passing a string through to Final Date rather than a true date...

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[terzaghi]

try with:
Sub test()

Dim d As Integer, m As Integer, y As Integer
d = Day(Worksheets("Sheet1").Cells(1, 1).Value)
m = Month(Worksheets("Sheet1").Cells(1, 1).Value)
y = Year(Worksheets("Sheet1").Cells(1, 1).Value)

Worksheets("Sheet1").Cells(2, 1).Value = d & " " & m & " " & y

End Sub

if the date is written correctly, no matter if in the format dd/mm/yy or mm/dd/yy this gets separately the three and then you can do whatever you like with them

 

[SkipVought]

terzaghi,

The ONLY reason such a formula might work is that Excel has some smarts built into it.

Here's what happens.

You enter a STRING value like 1/1/04

Excel EVALUATES the STRING and "says", A-Haaaaa, a Date! It takes the Regional Date Setting Format to determine whether its month/day or day/month, PARSES the STRING into YEAR (also smart enought to convert "04" to 2004 but DON'T count on it!), MONTH and DAY and GENERATES a DATE SERIAL NUMBER that is DSIPLAYED in some default FORMAT like "m/d/yyyy". The DATE SERIAL NUMBER is the REAL DATE!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[terzaghi]

Hi skip,

sorry but I guess I missed something as I don't understand.

I try so summarize: if I'm free to write in a cell a date, excel will interpret it in accordance of its regional settings. That's to say: I open excel with regional settings that say dd/mm/yyyy and I write 1/2/04 and the foirst time excel sees this cell it understand that the date is february, 1st, 2004. So. whatever formula I use after that the date is that.
If I do the same but with regional settings that says mm/dd/yyyy, I'll obtain january, 2nd, 2004. Correct? After that DateSerialNumber works in accordance with what Excel understood the first time I wrote the date. Is it correct?

I cannot say excel, whatever are the regional settings, how to interpret the date. After Excel has changed the date in its internal numeric format (a progressive real number) I can manipulate it with whatever formula I like.

Am I correct?

Thanks a lot
Andrea

 

[SkipVought]

The salient point in this entire discussion is that Dates are Numbers.

What you might ENTER or VIEW is merely a REPRESENTATION of the date based on regional setting/formats.

Too many MS users do not understand the difference, particularly what happens when STRING VALUES are entered. The precautionary warning is that Excel may NOT interpret what you enter in the way that you want.

For instance, if I run your code on my machine, with my settings, with 5/5/2005 in A1, I get "5 5 2005" in A2, which is NOT a date at all, it's just a STRING.

HOWEVER, if I GENERATE a DATE SERIAL NUMBER

Worksheets("Sheet1").Cells(3, 1).Value = DateSerial(y, m, d)

I get a REAL DATE!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ClydeDoggie]

To reinforce/enhance Skip's point:
(From the MSDN site)
The Date Data Type
VBA provides the Date data type to store date and time values. The Date data type is an 8-byte floating-point value, so internally it's the same as the Double data type. The Date data type can store dates between January 1, 100, and January 1, 9999.

VBA stores the date value in the integer portion of the Date data type, and the time value in the decimal portion. The integer portion represents the number of days since December 30, 1899, which is the starting point for the Date data type. Any dates before this one are stored as negative numbers; all dates after are stored as positive values. If you convert a date value representing December 30, 1899, to a double, you'll find that this date is represented by zero.

The decimal portion of a date represents the amount of time that has passed since midnight. For example, if the decimal portion of a date value is .75, three-quarters of the day has passed, and the time is now 6 P.M.

Because the integer portion of a date represents number of days, you can add and subtract days from one date to get another date.

 

[Rodie]

Hello everybody ...

Very complicated all that ... but I'm very obstinate !
xlbo, I tried exactly your code

01/07/04 in A1
07/01/04 in A2

Sub Test_Dates()
myDate1 = [A1]
myDate2 = [A2]
n = 10
myNewDate1 = format(myDate1 + n,"d m yyyy")
myNewDate2 = format(myDate2 + n,"d m yyyy")
msgbox "From UK Format : " & myNewDate1 & vbcrlf & "From US Format : " & myNewDate2
end sub

You will notice that you get the same answer for both

Of course, I obtained two differents dates :

From UK Format : 11 7 2004 (1 july + 10 = 11 july)
From US Format : 17 1 2004 (7 january + 10 = 17 january)

Indeed, you don't know if your computer understand dd mm yyyy or mm dd yyyy. With this result, mine understand dd mm yyyy...
Only SerialDate() allows the user to let know the computer which are the day and the month.

Rodie

 

[xlbo]

I'm very surprised you get 2 different dates - did you format the 2 cells as UK and US ie dd/mm/yyyy and mm/dd/yyyy as well as entering the dates differently

Should have A1 formatted as dd/mm/yyyy with 01/07/2004 and A2 formatted as mm/dd/yyyy with 07/01/2004....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Rodie]

Hi Geoff,

I'm sure you are very experienced , but I don't understand why you don't understand ...

Of course my 2 cells A1 and A2 are standard formatted (= I've opened an excel file and didn't touch anything ...)

What I mean is that :
Actually, Format() transforms a String into a date. But your String is read by the computer as a date according to his own date standard format.

If your computer is "dd mm yyyy":
string_date = "11 6 2004" = (11 june for the computer)
Format(string_date, "d m yyyy") displays 11 6 2004 (11 June)
Format(string_date, "m d yyyy") displays 6 11 2004 (11 June again !!)

It's logical : your computer always read "dd mm yyyy" and Format() only displays as you wish.

I hope it's clear enough. Anyway, I thank you a lot for your help... This forum impress me everyday thanks to you and the other Gosu ...

Rodie

 

[xlbo]

That was the whole point I was trying to make - it matters not what your regional settings are as all dates are numbers - therefore you don't need to know the regional settings as you can process all the dates as numbers to come to a standard answer - you then format as you wish afterwards

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Rodie.

Still believe you dont' understand. You stated

Format(string_date, "d m yyyy") displays 11 6 2004 (11 June)
Format(string_date, "m d yyyy") displays 6 11 2004 (11 June again !!)

This troubles me. I do not believe that you are working with a REAL date.

Run this macro

Sub DateTest()

string_date = "11 6 2004" '= (11 june for the computer)
[a1] = Format(string_date, "d m yyyy") 'displays  11 6 2004 (11 June)
[a2] = Format(string_date, "m d yyyy") 'displays  6 11 2004 (11 June again !!)Sub bn()

End Sub

select A1 & A2 and FORMAT the Cells General.

If these are REAL DATES the STRING display will change to NUMEIC.

They did NOT for me!!!

You do NOT have REAL DATES!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Rodie]

Hi Skip,

Effectively, I don't understand ... Sorry, I'm just a newbie...
My simple question was:
If you have a date "5 1 2004" (5 January 2004), how you can do if you want your computer to understand that "1" (January) is the month and not "5" (May).
Thanks to "DateSerial()", I can do that...

But the question of "String" and "Date" are not important for me. My program works now, even if I used dates and strings...

Thanks a lot to you... and forgive me if I'm totally off the mark since the beginning of the topic ...

Rodie

 

[SkipVought]

STRINGS are NOT DATES!!!

For instance, if you were to SORT your "DATES", they would NOT sort as you expect.

All you have is a string of characters that LOOK like dates, and maybe they fill our immediated need of stuff to DISPLAY like dates, but if your application begins to GROW, as most often do, you will sadly discover that your date capabilities are severely hampered, because you REALLY are not using DATES, but a very poor substitute.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244