Converting number into proper date format

  • Thread starter Thread starter Brandy
  • Start date Start date
B

Brandy

Hello,

I export data dumps from a file and the date always shows as 20090707 rather
than a proper date. Is there a formula that will help me convert this into a
proper date file?

I will then want to change that date into just a day of the week. Is that
possible?

Any help would be greatly appreciated.

Warm regards,

Brandy
 
With the date in A1 try the below in B1 and format B1 to excel date format

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

OR

Rightclick>FormatCells>Custom>
dddd
to display the day alone like Tuesday

OR

If you are looking at Day number of week try the below formula instead

=WEEKDAY(DATE(LEFT(E1,4),MID(E1,5,2),RIGHT(E1,2)))

If this post helps click Yes
 
Select the cells; use Data | Text to Columns;
Specify Fixed
Step 2: clcik after tlast 7 to add line
Step 3: check the Date box and select ymd (as that is the from you have in
20090707
done
 
For the date:

A1 = 20090707

B1 =

=--TEXT(A1,"000\/00\/00")

Format as Date

For the weekday:

=TEXT(B1,"ddd") - short format, returns Mon
=TEXT(B1,"dddd") - long format, returns Monday

Or, to convert A1 directly to the weekday:

=TEXT(--TEXT(A1,"000\/00\/00"),"ddd")
=TEXT(--TEXT(A1,"000\/00\/00"),"dddd")
 
Back
Top