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

Try this

=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","W ednesday","Thursday","Friday","Saturday")
--
HTH,
Barb Reinhardt



"Nils Titley" wrote:

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

On Sat, 22 Mar 2008 17:30:00 -0700, Nils Titley
wrote:

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

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

On Sat, 22 Mar 2008 17:30:00 -0700, Nils Titley
wrote:

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

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.

"Nils Titley" wrote:

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

On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley
wrote:

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

Ron,

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



"Ron Rosenfeld" wrote:

On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley
wrote:

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

On Sun, 23 Mar 2008 05:09:00 -0700, Nils Titley
wrote:

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

Ron,

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

"Ron Rosenfeld" wrote:

On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley
wrote:

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

Where did you get MyNum?



Nils Titley wrote:

Ron,

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

"Ron Rosenfeld" wrote:

On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley
wrote:

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


--

Dave Peterson

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

You need more?

"Dave Peterson" wrote:

Where did you get MyNum?



Nils Titley wrote:

Ron,

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

"Ron Rosenfeld" wrote:

On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley
wrote:

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


--

Dave Peterson

Dave,

I have been thinking that it might help if I tell you more about the process.

Each file will contain 1200 to 2000 rows of data. 10% of the data will not
be used. The data lists the date of pickup, the truck that made the pickup,
the lat and long and some other informtion. There will be in most cases
three runs per file. As I parse through each row in the worksheet, I am
totaling # of pickups, meters between pickups and the total number of bins
picked up and other data. After I parse all the rows, I generate three rows
to the report worksheet - one row for each run. So in reality, I only have
to convert three dates from the numerical format to a date format.

I have not tried the routine you provided earlier as of yet. But I have an
additional question. Since the date is a numerical value, could I store that
value in a Long veriable and convert to the date just before I print it since
that is the only time I need it in that form and it is the only piece of data
that is giving me a hard time. It seems like it would save processing and 3
out of 2000.

What do you think?

Thanks



"Dave Peterson" wrote:

Where did you get MyNum?



Nils Titley wrote:

Ron,

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

"Ron Rosenfeld" wrote:

On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley
wrote:

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


--

Dave Peterson

On Sun, 23 Mar 2008 05:14:00 -0700, Nils Titley
wrote:

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,

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

"Ron Rosenfeld" wrote:

On Sun, 23 Mar 2008 05:14:00 -0700, Nils Titley
wrote:

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