Change Excel system date and local system date.

[Kanzen]

Hi all,

This thread is extended from a thread that I had posted previously.
https://www.excelforum.com/excel-new...-for-date.html

On that post, Roel did talked about the difference between local system date and Excel system date format.
The difference is that in Excel, the date format is day-month-year, whereby my local system is month-day-year.

Anyone knows how to fix this?

[FDibbins]

...
The difference is that in Excel, the date format is day-month-year, whereby my local system is month-day-year....

Not so, and you probably misunderstood Roel.

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sun 31 Mar 2019) is actually 43555

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So to answer your question, all you need to do is adjust the cell formatting under Home/Number/Date

[Kanzen]

Hi FDibbins,

Thank you for the explanation.

So let say I copied a date from another system and it shows 12/2/2019, which logically it means 12 February 2019, but Excel formatted it as 2 December 2019, and with the date value of 45801.
The problem is that I want Excel to show that as 12 February, which is 43508.
So it means I have to convert 45801 to 43508.
Anyway I can do so?

[FDibbins]

Which system are you copying from?
If you bring the data across in TEXT format, you can use Text2Columns to do the conversion for you.

[Kanzen]

I downloaded the data from a bookkeeping system.
Then, I copy them into an existing database.
After copying them into existing database, I want to separate the date into two columns, "Year" and "Month".
123.PNG
The problem arisen because there are 3 types of date here.
For example:
1. 31/12/2018 - cell format is 'date' (which this means 31 December 2018)
2. 02/12/2018 - cell format is 'date' and date value is 43508 (the problem here is it should be 2 Dec 2018, but shown as 12 Feb 2018)
3. 16/11/2018 - cell format is 'general' (the problem here is it should be a date value (eg. 43508) when the cell format is 'general)

These three different format of text messed me up when I was trying to use formula to extract them into another column.

Btw, I do not understand what is Text2Columns, do you have a link that I can learn from?

[Kanzen]

Thank you FDibbins.

After hours of checking through the function text-to-column, I finally get my problem solved.

[FDibbins]

Apologies, it was getting late here, but Im happy you got what you wanted, thanks for the feedback