Dates

[Khalil Handal]

Cell D14 contains a date with format DD/MM/YY
I want to split it into the cells E14, F14, G14
Where E14 contains the DD part, F14 containes the MM part and G14 contains
the YY parts.
What formula should I use?

 

[Sandy Mann]

=Day(D14)
=Month(D14)
=Year(D14)
--
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

 

[Dave Peterson]

Maybe...

=day(d14)
=month(d14)
=year(d14)

to extract the day, month, year.

 

[Don Guillett]

=text(d14,"ddd")
=text(d14,"mmm")
=year(d14)

 

[Guest]

You could just put =D14 in each cell and format for d,m, and yyyy

Vaya con Dios,

Chuck, CABGx3

 

[Khalil Handal]

If no date is in the cell i will have 0 for the days, 1 for the months and 0
for the years.
How can I rewrite the formulas so as it will leave the cells empty if no
date is inserted.
The formulas that worked for me are:
=DAYS(D14)
=MONTH(D14)
=D14 and formated the cell to date with YY.

Thank you all

 

[Dave Peterson]

=if(d14="","",days(d14))
=if(d14="","",month(d14))
=if(d14="","",text(year(d14),"yy"))

 

[Sandy Mann]

=if(d14="","",days(d14))

DAYS()?

Is this some function I don't know about?

--

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

 

[Dave Peterson]

Did you test it? <bg>.

Or it could be a typo.

DAYS()?

Is this some function I don't know about?

--

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

 

[Sandy Mann]

Did you test it? <bg>.
No! of course I didn't - well OK I checked the fx Paste Function list first
just in case it was egg on face time again.

I didn't add a small grin because I have never installed the Analysis
ToolPack so I wan't quite sure if it wasn't included in there.

--

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

 

[Khalil Handal]

I tried this format taking it from one sheet to another, it worked for the
day but gave 1 for the month:
=IF(IF(DAY(Marks!E20<>""),DAY(Marks!E20),"")
where E20 is the whole date format as mm/dd/yyyy

I will try your suggestions

Thanks

Did you test it? <bg>.

No! of course I didn't - well OK I checked the fx Paste Function list
first just in case it was egg on face time again.

I didn't add a small grin because I have never installed the Analysis
ToolPack so I wan't quite sure if it wasn't included in there.

--

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

 

[Sandy Mann]

You don't need the second IF() just:

=IF(Marks!E20<>"",DAY(Marks!E20),"")
will do it.

=IF(Marks!E20<>"",MONTH(Marks!E20),"")
will return the Month number did you want the month name? If so use Don's
formula or simply =Marks!E20 and custom format the cell as mmm
for a three letter name or mmmm for the full name.

--
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

I tried this format taking it from one sheet to another, it worked for the
day but gave 1 for the month:
=IF(IF(DAY(Marks!E20<>""),DAY(Marks!E20),"")
where E20 is the whole date format as mm/dd/yyyy

I will try your suggestions

Thanks

 

[Khalil Handal]

The second "if" was a mistake in typing.

You don't need the second IF() just:

=IF(Marks!E20<>"",DAY(Marks!E20),"")
will do it.

=IF(Marks!E20<>"",MONTH(Marks!E20),"")
will return the Month number did you want the month name? If so use Don's
formula or simply =Marks!E20 and custom format the cell as mmm
for a three letter name or mmmm for the full name.

--
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