Day/Date formula
 

Is it possible to have a function where if you enter the date numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure it gets
the day of the week correct.

Thanks

Day/Date formula
 

Data Text to Columns Next Next select Date: DMY Finish

Custom Format
ddd, d mmmm yyyy


"86" wrote:

Is it possible to have a function where if you enter the date numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure it gets
the day of the week correct.

Thanks

Day/Date formula
 

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure it gets
the day of the week correct.

Thanks

Day/Date formula
 

Awesome - thanks!

What about if I need to enter more than one date in the same cell?
eg '4-6/5/7' returns 'Fri, 4 - Sun, 6 May 2007'
or '4/5/7 - 6/5/7' returns 'Fri, 4 May 2007 - Sun, 6 May 2007'

I tried 'ddd, d mmmm yyyy - ddd, d mmmm yyyy' but that didn't work!

If the above is possible, can I instruct the software to distnguish between
single dates & doubles in the same column?

What I'm doing is making up a calander for a scout group, dates in the A
column.
Some activities go for one day, some others go for a weekend, week, etc.

In the past I've entered dates manually but when rushed I easily make
mistakes.
Having this date column automated would hopefully eleviate these mistakes!

Thanks

"Teethless mama" wrote:

Data Text to Columns Next Next select Date: DMY Finish

Custom Format
ddd, d mmmm yyyy


"86" wrote:

Is it possible to have a function where if you enter the date numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure it gets
the day of the week correct.

Thanks

Day/Date formula
 

Oh well, I can do without the th's, nd's st's, etc!
Thanks heaps for replying.
Any ideas on my 2nd post?

Cheers


"BoniM" wrote:

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure it gets
the day of the week correct.

Thanks

Day/Date formula
 

For your 2nd post and with the start date in F1 and the end date in F2, try
somthing like:

=CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu", "Fri","Sat")&",
"&DAY(F1)&" -
"&CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat") &",
"&DAY(F2)&" "&
TEXT(CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May ","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),"???" )&"
"&YEAR(F2)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"86" wrote in message
...
Oh well, I can do without the th's, nd's st's, etc!
Thanks heaps for replying.
Any ideas on my 2nd post?

Cheers


"BoniM" wrote:

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date
numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure it
gets
the day of the week correct.

Thanks

Day/Date formula
 

Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
For your 2nd post and with the start date in F1 and the end date in
F2, try somthing like:

=CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu", "Fri","Sat")&",
"&DAY(F1)&" -
"&CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat") &",
"&DAY(F2)&" "&
TEXT(CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May ","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),"???" )&"
"&YEAR(F2)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"86" wrote in message
...
Oh well, I can do without the th's, nd's st's, etc!
Thanks heaps for replying.
Any ideas on my 2nd post?

Cheers


"BoniM" wrote:

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date
numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to
ensure it gets
the day of the week correct.

Thanks

Day/Date formula
 

Roger Govier" wrote in message
... Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

Very good Roger. I assume that the =IF(F2="", is a typo for =IF(F2<"", ?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Roger Govier" wrote in message
...
Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
For your 2nd post and with the start date in F1 and the end date in F2,
try somthing like:

=CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu", "Fri","Sat")&",
"&DAY(F1)&" -
"&CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat") &",
"&DAY(F2)&" "&
TEXT(CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May ","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),"???" )&"
"&YEAR(F2)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"86" wrote in message
...
Oh well, I can do without the th's, nd's st's, etc!
Thanks heaps for replying.
Any ideas on my 2nd post?

Cheers


"BoniM" wrote:

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date
numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure
it gets
the day of the week correct.

Thanks

Day/Date formula
 

No Sandy,

If there is no date entered in F2, then just use F1 as Fri, 30 Mar 07.
If there is a date in F2, then use the concatenation of a shortened form
of F1 with " to " and the longer form of F2 date.
as in Fri, 30 Mar to Tue, 03 Apr 07.
--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
Roger Govier" wrote in message
... Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

Very good Roger. I assume that the =IF(F2="", is a typo for
=IF(F2<"", ?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Roger Govier" wrote in message
...
Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
For your 2nd post and with the start date in F1 and the end date in
F2, try somthing like:

=CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu", "Fri","Sat")&",
"&DAY(F1)&" -
"&CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat") &",
"&DAY(F2)&" "&
TEXT(CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May ","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),"???" )&"
"&YEAR(F2)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"86" wrote in message
...
Oh well, I can do without the th's, nd's st's, etc!
Thanks heaps for replying.
Any ideas on my 2nd post?

Cheers


"BoniM" wrote:

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date
numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to
ensure it gets
the day of the week correct.

Thanks

Day/Date formula
 

"Sandy Mann" wrote in message
...
Roger Govier" wrote in message
... Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

Very good Roger. I assume that the =IF(F2="", is a typo for =IF(F2<"", ?

No of course you didn't! I don't know what I was thinking of, my apologies


--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Roger Govier" wrote in message
... Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

Very good Roger. I assume that the =IF(F2="", is a typo for =IF(F2<"", ?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Roger Govier" wrote in message
...
Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
For your 2nd post and with the start date in F1 and the end date in F2,
try somthing like:

=CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu", "Fri","Sat")&",
"&DAY(F1)&" -
"&CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat") &",
"&DAY(F2)&" "&
TEXT(CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May ","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),"???" )&"
"&YEAR(F2)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"86" wrote in message
...
Oh well, I can do without the th's, nd's st's, etc!
Thanks heaps for replying.
Any ideas on my 2nd post?

Cheers


"BoniM" wrote:

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date
numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure
it gets
the day of the week correct.

Thanks

Day/Date formula
 

Yes, so I just realised. As I said in my 2nd post, I have no idea what I
was thinking about. Once again my apologies.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Roger Govier" wrote in message
...
No Sandy,

If there is no date entered in F2, then just use F1 as Fri, 30 Mar 07.
If there is a date in F2, then use the concatenation of a shortened form
of F1 with " to " and the longer form of F2 date.
as in Fri, 30 Mar to Tue, 03 Apr 07.
--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
Roger Govier" wrote in message
... Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

Very good Roger. I assume that the =IF(F2="", is a typo for =IF(F2<"",
?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Roger Govier" wrote in message
...
Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
For your 2nd post and with the start date in F1 and the end date in F2,
try somthing like:

=CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu", "Fri","Sat")&",
"&DAY(F1)&" -
"&CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat") &",
"&DAY(F2)&" "&
TEXT(CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May ","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),"???" )&"
"&YEAR(F2)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"86" wrote in message
...
Oh well, I can do without the th's, nd's st's, etc!
Thanks heaps for replying.
Any ideas on my 2nd post?

Cheers


"BoniM" wrote:

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date
numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to ensure
it gets
the day of the week correct.

Thanks

Day/Date formula
 

Hi Sandy

Absolutely no need for any apology.
I misread things all the time!!! I put it down to my age - those senior
moments have an increasing frequency<bg
Best wishes.

--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
Yes, so I just realised. As I said in my 2nd post, I have no idea
what I was thinking about. Once again my apologies.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Roger Govier" wrote in message
...
No Sandy,

If there is no date entered in F2, then just use F1 as Fri, 30 Mar
07.
If there is a date in F2, then use the concatenation of a shortened
form of F1 with " to " and the longer form of F2 date.
as in Fri, 30 Mar to Tue, 03 Apr 07.
--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
Roger Govier" wrote in message
... Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

Very good Roger. I assume that the =IF(F2="", is a typo for
=IF(F2<"", ?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Roger Govier" wrote in message
...
Hi

If 2 cells are going to be used to hold the dates, then
=IF(F2="",TEXT(F1,"ddd, dd mmm yy"),TEXT(F1,"ddd, dd")
&" to "&TEXT(F2,"ddd, dd mmm yy"))

--
Regards

Roger Govier


"Sandy Mann" wrote in message
...
For your 2nd post and with the start date in F1 and the end date
in F2, try somthing like:

=CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu", "Fri","Sat")&",
"&DAY(F1)&" -
"&CHOOSE(WEEKDAY(F1),"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat")
&", "&DAY(F2)&" "&
TEXT(CHOOSE(MONTH(F2),"Jan","Feb","Mar","Apr","May ","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),"???" )&"
"&YEAR(F2)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"86" wrote in message
...
Oh well, I can do without the th's, nd's st's, etc!
Thanks heaps for replying.
Any ideas on my 2nd post?

Cheers


"BoniM" wrote:

Under format cells, number, custom format, enter:
ddd, d mmmm yyyy
to display April 1st:
Sun, 1 April 2007

ddd, dd mmmm yyyy
for this:
Sun, 01 April 2007

I don't know of any way to display ordinal numbers within a
date:
Sun, 1st April 2007
as you have in your sample.

"86" wrote:

Is it possible to have a function where if you enter the date
numerically, eg
'30/3/7'
it returns the DAY & date, eg 'Fri, 30th March 2007' ?

This would obviously work off the computer calander so as to
ensure it gets
the day of the week correct.

Thanks