Dates value

[Hassan1977]

Bit puzzled her..

when I copy an paste (only Value) I don't get the same result for both lines

28/01/2015 28/01/2015
2/3/2015 42038

any ideas why, please?

Thank you

[FoxSeaLady]

Hi

You will need to check the format of the cells to "Date"

Cheers

[Tony Valko]

42038 is the date serial number for 2/3/2015.

You just need to format the cell as Date.

In Excel dates are really just numbers formatted to look like dates. These numbers are commonly referred to as the date serial number. The date serial number is the count of days starting from a base date. In Windows Excel the default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Jan 1 2016 = date serial number 42370

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

[Hassan1977]

42038 is the date serial number for 2/3/2015.

You just need to format the cell as Date.

In Excel dates are really just numbers formatted to look like dates. These numbers are commonly referred to as the date serial number. The date serial number is the count of days starting from a base date. In Windows Excel the default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Jan 1 2016 = date serial number 42370

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

Thanks for the extensive answer, unfortunately I still have issue unresolved even when I change the formatting.

Please see the attachment

Thanks

[Tony Valko]

I downloaded your file.

Cell B6 contained the number value 42038 and the cell was formatted as General.

I changed the cell format to Date and the cell displayed the date 2/3/2015 (m/d/yyyy).

[Hassan1977]

I downloaded your file.

Cell B6 contained the number value 42038 and the cell was formatted as General.

I changed the cell format to Date and the cell displayed the date 2/3/2015 (m/d/yyyy).

the issue is with A5 when I change it to number Value it stays the same ..?! result in B5

[Tony Valko]

When I open the file cell A5 contains the TEXT string 28/01/2015 and the cell is formatted as General.

My regional date format is m/d/yyyy. Usually, the dates should convert to the settings of the recipient of the file but it did not happen this time for whatever reason.

If I manually re-enter the date as 1/28/2015 then it converts to the date value of 1/28/2015.

If you have a lot of these you can convert them to true numeric dates by...

Select the cells in question
Goto the Data tab>Text to Columns
Click Next twice
In Step 3 of the userform under Column data format select Date and in the drop down select DMY
Click Finish

Sometimes this will convert TEXT dates into numeric dates.

[Hassan1977]

When I open the file cell A5 contains the TEXT string 28/01/2015 and the cell is formatted as General.

My regional date format is m/d/yyyy. Usually, the dates should convert to the settings of the recipient of the file but it did not happen this time for whatever reason.

If I manually re-enter the date as 1/28/2015 then it converts to the date value of 1/28/2015.

If you have a lot of these you can convert them to true numeric dates by...

Select the cells in question
Goto the Data tab>Text to Columns
Click Next twice
In Step 3 of the userform under Column data format select Date and in the drop down select DMY
Click Finish

Sometimes this will convert TEXT dates into numeric dates.

yes I have many of them on a column, the issue is they are mixed formats as well as having the text formats, . see attached,

my aim is to have all of them in one Date Format

Thanks

[Tony Valko]

You could use a formula to convert all to dates.

Entered in B2:

=IF(N(A2),A2,DATE(RIGHT(A2,4),SUBSTITUTE(MID(A2,FIND("/",A2),3),"/",""),LEFT(A2,FIND("/",A2)-1)))

Format as Date

Copy down as needed.

[Hassan1977]

You could use a formula to convert all to dates.

Entered in B2:

=IF(N(A2),A2,DATE(RIGHT(A2,4),SUBSTITUTE(MID(A2,FIND("/",A2),3),"/",""),LEFT(A2,FIND("/",A2)-1)))

Format as Date

Copy down as needed.

Excellent one !! Thank you!

[Tony Valko]

You're welcome. Thanks for the feedback!