Excel Date separate with DAY MONTH and YEAR

0 votes

I am having trouble with the functions =DAY(), =MONTH(), and =YEAR, even though it is a fairly simple question (). It fails and gives me an error. I reject CSV.

Excel Sheet:

A1 19.03.2023
A2 =DAY(A1)

but A2 gives #VALUE error. The Format is in General for A1 and A2 even if I set to Date nothing changes.

Mar 20, 2023 in Others by Kithuzzz
• 38,010 points
250 views

1 answer to this question.

0 votes

If it must be done inside of Excel, one approach is to format the data using SPLIT and CONCAT. If necessary, the CONCAT might even be rearranged.

TEXTSPLIT(A1,".")

CONCAT(B2,"/",C2,"/",D2)

Excel

Another approach, and the one I use 99/100, is to import the data into Power Query, perform any necessary transformations, and then export the cleaned-up and ready-to-use data to another page (or even another workbook).

Assuming that you actually have a header "Date" in A1, and the data in question was in A2:

  1. Ctrl+T to turn it into a Table
  2. From the Data tab, "Get Data from Table/Range"
  3. From within Power Query, right-click the Date column, select "Replace values".
  4. Value to Find "."
  5. Replace with (leave blank)
  6. Results: 19032023

Power Query 1

answered Mar 20, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Looping through a folder with Excel files and scraping date from selected cells usin VBA

You record the outcome of your comparison ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,420 points
614 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,420 points
511 views
0 votes
1 answer

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,420 points
1,333 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
835 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,090 points
787 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,090 points
4,021 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,733 views
0 votes
1 answer

Excel shortcut for month day year short date [closed]

What about Ctrl-1 + Tab + Tab + ...READ MORE

answered Jan 3, 2023 in Others by narikkadan
• 63,420 points
314 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP