Dates

[How_Do_I]

Could anyone help me out with these please. I have two formats of dates, the 1st format is in B2 and is monthly... I'm after the returns in C2 and D2 which are the first and last days of the respective months. The 2nd format is dates that need the format altering to day / month / year. I'm not bothered if helper columns are added to achieve the desired solution...

	Date	Start Date	End Date
1st Format	1916/01-1917/11	01-Jan-1916​	30-Nov-1917​
2nd Format	1915/12/24-1916/07/22	24-Dec-1915​	22-Jul-1916​

 

[Special-K99]

Not enough information.

1916/01 - there is no day in this field
1917/11 - there is no day in this field either

You are implying for the missing day information that
Start Date, e.g. 1916/01 - result required, assume first day of the month
End Date, e.g. 1917/11 - result required, assume last day of the month

So given the format
1916/01-1917/11

The first of the day of the month should be added to the start date
and the last day of the month should be added to the end date

Will this ALWAYS be the case?

 

[Special-K99]

Assuming it is ALWAYS the case


Start date
=IF(LEN(B1)=15,(LEFT(B1,7)&"/01")+0,LEFT(B1,10)+0)

End Date
=IF(LEN(B1)=15,(EOMONTH(RIGHT(B1,7)&"/01",0))+0,RIGHT(B1,10)+0)

where B1 is the date string

 

[Toadstool]

Hi How_Do_I,

You are dangerously close to 1st January 1900 (the earliest date Excel will process with its date functions) but handling leap years is tricky if you're trying to just manipulate as text, so please confirm no dates are before 1900. Fortunately the day and month have leading zeros so that simplifies the challenge.

You don't say if the sample dates are in different columns so my row 2 and 3 formulae just handle that format. The row 4 and 5 formulae use the length to check which date format is contained in column B.

How_Do_I.xlsx
	A	B	C	D
1		Date	Start Date	End Date
2	1st Format	1916/01-1917/11	01-Jan-1916	30-Nov-1917
3	2nd Format	1915/12/24-1916/07/22	24-Dec-1915	22-Jul-1916
4	Mix	1901/11-1901/12	01-Nov-1901	31-Dec-1901
5	Mix	1901/01/01-1902/01/01	01-Jan-1901	01-Jan-1902
Sheet1

Cell Formulas
Range	Formula
C2	C2	=TEXT(DATE(MID(B2,1,4),MID(B2,6,2),1),"dd-mmm-yyyy")
D2	D2	=TEXT(EOMONTH(DATE(MID(B2,9,4),MID(B2,14,2),1),0),"dd-mmm-yyyy")
C3	C3	=TEXT(DATE(MID(B3,1,4),MID(B3,6,2),MID(B3,9,2)),"dd-mmm-yyyy")
D3	D3	=TEXT(DATE(MID(B3,12,4),MID(B3,17,2),MID(B3,20,2)),"dd-mmm-yyyy")
C4:C5	C4	=IF(LEN(B4)<16,TEXT(DATE(MID(B4,1,4),MID(B4,6,2),1),"dd-mmm-yyyy"),TEXT(DATE(MID(B4,1,4),MID(B4,6,2),MID(B4,9,2)),"dd-mmm-yyyy"))
D4:D5	D4	=IF(LEN(B4)<16,TEXT(EOMONTH(DATE(MID(B4,9,4),MID(B4,14,2),1),0),"dd-mmm-yyyy"),TEXT(DATE(MID(B4,12,4),MID(B4,17,2),MID(B4,20,2)),"dd-mmm-yyyy"))

 

[How_Do_I]

Assuming it is ALWAYS the case

Yes always the case, if there's no day number in the data... Thanks for your help with this...!

 

[How_Do_I]

Hi How_Do_I,

You are dangerously close to 1st January 1900...

Hello Toadstool, yes all my dates are after August 1914, thanks for your help with this...!