Day/Date formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Data > Text to Columns > Next > Next > select Date: DMY > Finish

Custom Format
ddd, d mmmm yyyy
 
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.
 
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
 
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
 
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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"))
 
Roger Govier said:
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"))
Click to expand...
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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.
 
Sandy Mann said:
Very good Roger. I assume that the =IF(F2="", is a typo for =IF(F2<>"", ?
Click to expand...

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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.
 
Back
Top