Convert the date to day of the week.

[Nils Titley]

I need to convert the date 22/03/08 which is dd/mm/yy to what day it is which
is Saturday. Is there a function that does that? Or how can I do what I
want?

Thanks

 

[Barb Reinhardt]

Try this

=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

 

[Ron Rosenfeld]

I need to convert the date 22/03/08 which is dd/mm/yy to what day it is which
is Saturday. Is there a function that does that? Or how can I do what I
want?

Thanks

You can format the cell (Format/Cells/Number/Custom Type: "dddd")

or you can use a formula:

=TEXT(cell_ref,"dddd")
--ron

 

[Gord Dibben]

If you just want to see the Saturday, format as dddd

If you want to actually convert, see other replies.


Gord Dibben MS Excel MVP

 

[Nils Titley]

Thanks to all for responding but I am wondering. Will I have a problem with
12/02/08. This is not December 02,08 but it is Feb 12, 08. The dd/mm/yy has
already caused a problem. The macro is being used in South Africa. I had to
force format on a date because it was changing the format.

The code below produced 02/12/08 when it is 12/02/08. It worked only after
I changed below to Format(RunDate(MyNum), "dd/mm/yy")

With ActiveCell.Offset(0, 0)
.NumberFormat = "dd/mm/yy"
.Value = RunDate(MyNum)
.HorizontalAlignment = xlCenter

How do I force the day of the week?

Thanks , I hope I am making sense.

 

[Ron Rosenfeld]

Thanks to all for responding but I am wondering. Will I have a problem with
12/02/08. This is not December 02,08 but it is Feb 12, 08. The dd/mm/yy has
already caused a problem. The macro is being used in South Africa. I had to
force format on a date because it was changing the format.

The code below produced 02/12/08 when it is 12/02/08. It worked only after
I changed below to Format(RunDate(MyNum), "dd/mm/yy")

With ActiveCell.Offset(0, 0)
.NumberFormat = "dd/mm/yy"
.Value = RunDate(MyNum)
.HorizontalAlignment = xlCenter

How do I force the day of the week?

Thanks , I hope I am making sense.

I believe you may be misunderstanding how dates are being entered into Excel.

Excel stores dates as serial numbers with 1 = 1 Jan 1900. It then formats that
result depending on the cell format.

However, a date expressed like 12/02/08 is ambiguous. So when you enter that
value into a cell, Excel has to decide if you mean 12 Feb or 2 Dec. It does
this by looking at the date settings -- NOT in Excel -- but at Control
Panel/Regional and Language Options.

So if your code is producing 2 Dec when you expect it to be producing 12 Feb,
you need to look at the output of RunDate(MyNum). If the numeric value is
39490, then it is returning 12 Feb; if 39784, then 2 Dec.

If it is returning the desired date, then
.numberformat = "dddd" will return the correct day of the week.
--ron

 

[Nils Titley]

Ron,

Okay, but will setting the international xlDateOrder help? And how do I do
that in my Macro.

 

[Nils Titley]

Ron,

I confess, I don't know how to get the numeric value of it?

 

[Dave Peterson]

Where did you get MyNum?

Ron,

I confess, I don't know how to get the numeric value of it?

 

[Nils Titley]

RunDate = Dim RunDate(0 To 25) As Date
MyNum = Dim MyNum As Long

You need more?

 

[Ron Rosenfeld]

Ron,

I confess, I don't know how to get the numeric value of it?

Perhaps this will help:


=====================================
Option Explicit
Sub foo()
Const dt As Date = #2/12/2008#
Const dt2 As Date = #12/2/2008#

Debug.Print CLng(dt), Format(dt, "dddd, dd-mmm-yyyy")
Debug.Print CLng(dt2), Format(dt2, "dddd, dd-mmm-yyyy")

End Sub
====================================
--ron

 

[Ron Rosenfeld]

Ron,

Okay, but will setting the international xlDateOrder help? And how do I do
that in my Macro.

As I wrote before, you need to determine what your input is and insure that

RunDate(MyNum) is returning the proper date.

Excel can usually convert between one date format and another, but you need to
be certain that your RunDate function is returning an unambiguous date.
--ron

 

[Dave Peterson]

Yep.

Does the user type it in? Do you pick it up from a cell? Do you import it from
a text file?

 

[Nils Titley]

Ron,

I am not sure this is helping....

I changed what you have because I don't have #2/12/2008# I have "02/12/2008".

When I do that I get debug print
A) 02/12/2008 = 39490 120208 (ddmmyy)
B) 12/02/2008 = 39784 021208 (ddmmyy)

A) ActiveCell.Offset(0, 17).Value = Format(CLng(dt), "ddmmyy")
B) ActiveCell.Offset(0, 18).Value = Format(CLng(dt2), "ddmmyy")
Results
A) 120208
B) 21208

I tried, what I call casting, RunDate(MyNum) as
CLng(RunDate(MyNum))

ActiveCell.Offset(0, 0).Value = Format(CLng(RunDate(MyNum)), "ddmmyy")
Data = 12/02/2008
Result = 21208
Expecte as above 120208

Please, I don't get it and don't know what to do.

Thanks

 

[Dave Peterson]

No response????

Yep.

Does the user type it in? Do you pick it up from a cell? Do you import it from
a text file?

 

[Nils Titley]

Dave,

I am sorry. I missed you replay. This is the same macro I have been
working on.

The data are in CSV excel files. I am taking the data from the file and
crunching the numbers and output it to another workbook. The dates are in
the 12/2/2008 which is ddmmyyyy. All the dates are in that format.
12/2/2008 is the first problem I have encoutered with the date. It things it
is mmddyyyy.

Gets loads the variable.
RunDate(Beat) = ActiveCell.Offset(0, 1).Value

Any other questions?

Thanks and I am sorry I missed your question.

No response????

 

[Dave Peterson]

If you're dates in the CSV file are in dmy order, then I would rename the
workbook to *.txt. Then I could import the data and specify that field as a
date in dmy order.

I _think_ that what's happening is that other users have a different data
order. The data that could pass for dates in that formatted will be converted
to a real date -- but probably not what you want.

For instance if the data shows: 12/02/2008
and you bring it in with your shortdate format of dmy, then you'll see a date of
Feb 12, 2008.

But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2,
2008.

The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't
tell if the date is really want the CSV intended.

And those "dates" that do not look like dates that match the users short date
format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25,
2008.

For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a
date.

So my recommendation is to not rely on the user's setting. Bring the data in a
different way--either by renaming the file as *.txt (so you see the wizard).

You could also try: Data|Import external data|get data to see the same text to
columns wizard.

====
My real point is just because the value looks like a date doesn't mean that it
is a date. And even if it is a date, it may not be what the original data
intended.

Dave,

I am sorry. I missed you replay. This is the same macro I have been
working on.

The data are in CSV excel files. I am taking the data from the file and
crunching the numbers and output it to another workbook. The dates are in
the 12/2/2008 which is ddmmyyyy. All the dates are in that format.
12/2/2008 is the first problem I have encoutered with the date. It things it
is mmddyyyy.

Gets loads the variable.
RunDate(Beat) = ActiveCell.Offset(0, 1).Value

Any other questions?

Thanks and I am sorry I missed your question.

 

[Nils Titley]

Dave,

The data is not in the form 25/02/2008 it is 25022008.

I am going to have to process 60+ files.

Is there a quick way to rename the files to .txt?

And the last part to try. I don't get because I could not find columns
wizard.

There has to be a better way to do a conversion.

Do you know anything about xlDateOrder application.international?

I don't what else to do or how to move forward. I am almost done with what
I need to do and this has set me back.

Thanks

If you're dates in the CSV file are in dmy order, then I would rename the
workbook to *.txt. Then I could import the data and specify that field as a
date in dmy order.

I _think_ that what's happening is that other users have a different data
order. The data that could pass for dates in that formatted will be converted
to a real date -- but probably not what you want.

For instance if the data shows: 12/02/2008
and you bring it in with your shortdate format of dmy, then you'll see a date of
Feb 12, 2008.

But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2,
2008.

The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't
tell if the date is really want the CSV intended.

And those "dates" that do not look like dates that match the users short date
format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25,
2008.

For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a
date.

So my recommendation is to not rely on the user's setting. Bring the data in a
different way--either by renaming the file as *.txt (so you see the wizard).

You could also try: Data|Import external data|get data to see the same text to
columns wizard.

====
My real point is just because the value looks like a date doesn't mean that it
is a date. And even if it is a date, it may not be what the original data
intended.

 

[Nils Titley]

Ok, I found the wizard but I can't do that with 60+ files every day. But I
don't think that is what you mean.

Dave,

The data is not in the form 25/02/2008 it is 25022008.

I am going to have to process 60+ files.

Is there a quick way to rename the files to .txt?

And the last part to try. I don't get because I could not find columns
wizard.

There has to be a better way to do a conversion.

Do you know anything about xlDateOrder application.international?

I don't what else to do or how to move forward. I am almost done with what
I need to do and this has set me back.

Thanks

 

[Dave Peterson]

If the value in the CSV file is 25022008, then it's not a date (to excel!).

It's just an 8 digit number.

It's up to you to convert it to a date.

You could record a macro to select the column and then data|text to columns.
You can specify that this field is a date and is in dmy order. After you
convert that 8 digit number to a real date, you can format the column in any
date format that you want.

If you're opening the file in a macro, then you could add this step (for each
field that needs it!) right after you open the file.

Dave,

The data is not in the form 25/02/2008 it is 25022008.

I am going to have to process 60+ files.

Is there a quick way to rename the files to .txt?

And the last part to try. I don't get because I could not find columns
wizard.

There has to be a better way to do a conversion.

Do you know anything about xlDateOrder application.international?

I don't what else to do or how to move forward. I am almost done with what
I need to do and this has set me back.

Thanks