Free Microsoft Excel 2013 Quick Reference

Break/Split Date Into Day, Month & Year

Hi
In Column A, I have a date formatted as follows: 10/13/2007
I want to break this out into three other columns as follows:
Column B Month = 10
Column B Day = 13
Column B Year = 2007

I need all three columns to be values that I can use in a VLOOKUP.

Thanks
LPS


Post your answer or comment

comments powered by Disqus
Hi all,

How to convert date into day of year. Like 31-Jan-2012 would be 31 day of year and 29-feb-2012 would be 60 day of year ..???

How do I change excel's date formatting so that when it opens up a CSV file with dates in day/month/year format, it shows up correctly in the date format? Currently, all dates with days over 12 are fine, but equal to or less than 12 excel is reformatting the day data into the month place.

I would like to enter date in day-month-year in an excel sheet and it should
accept accordingly. can anyone please tell me if you know. thanks in advance

Hello,

i need to put a date into a month +year
like this:
1/06/2008jun/08

i did this with a format date.
After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date.

MaandTotal
jun/081jun/0810

he sees that 1/6/2008 is not the same as 2/6/2008
that is why i got 2 lines of jun

but i just want one and that he counts all the junes together

anyone

kind regards

How to calculate difference between two dates in days, months and year. Please refer the attached document.

Hello,

I need assistance, please (anyone!!).
I have a spreadsheet where I have run a query and pulled an export to Excel. The report is very simple (below). Basically, I am trying to convert the second column from Day/Month/Year to Month/Day/YEAR with the allocated time listed. If I go and paste this information into excel (since it is an export) there are constraints in the cell that do not allow me to do the general format options and change the column into the correct format. Nothing works.
Any help here?

Created Updated Resolved
12/13/2011 13/12/11 16:07 12/13/2011 0:04:00

Hi...!

I have employee data where I would like to count No. of days , Month & years in service from the date of joining & given(or say system) date.

Pls. Pls help with excel magic.

Thanks in advance, PBChhaya

I have a column of data with a date in each row in the format 01/04/2004 and I need to make in a new column data from the original column that only gives Apr-04 so I can group by month/year. Currently when I pivot table it will only group by day/month/year.

Thanks

I would like to know how would I work out someone's age in number ("20")
from having three seperate columns of:

Day Month Year
14 March 1986

note, this is not in the form dd/mm/yyyy in *one* cell but in three
seperate columns. I am thinking there may be two steps e.g. on step to
get it into dd/mm/yyyy then another to pur it into age?

Thanks in advance, Tim

--
timmyc
------------------------------------------------------------------------
timmyc's Profile: http://www.excelforum.com/member.php...o&userid=31198
View this thread: http://www.excelforum.com/showthread...hreadid=508699

1) I have some charts where the X-Axis scale is calibrated by entering dates,
and minor/major tick marks are in whole days/months/years.

2) I have similar charts where the X-Axis is calibrated using numbers that
represent dates (i.e. 39287 for 7/24/07) and I can use numbers with multiple
decimal places to set the major minor tick marks.

How can I convert (1) to (2)?

Chart (1) is difficult to use because the dates never line up as I want
(with a specified beginning and ending date). Chart (2) does allow me to set
a specified date at each end of the X-Axis.

I would like to know how would I work out someone's age in number ("20") from having three seperate columns of:

Day Month Year
14 March 1986

note, this is not in the form dd/mm/yyyy in one cell but in three seperate columns. I am thinking there may be two steps e.g. on step to get it into dd/mm/yyyy then another to put it into age?

Thanks in advance, Tim

Hello

My PC is set up as UK region and hence displays dates as 20/01/06. However
I have a spreadsheet sent from the US formatted as general and reflects
01/20/06 as the date. I am trying to use a vlookup where the dates match, I
have tried format cells, date dd-mm-yy but this does not work it still shows
01/20/06.

I have also tried to use datevalue, day, month, year functions (as a serial
number) and then looking up those values. However they all return #value.
Does anyone have any ideas how I can convert this date to UK date format?

Any help is appreciated.

Thanks.

Excel 2003

My workbook has a worksheet that lists service activities and a 2nd worksheet that contains a month-year column and an associated Customer PO# to be assigned to an activity based on its Open Date...if it is a specific type of activity.

My logic statement is IF activity type RO is "X", then match its Open Date to the Month-Year in the AssignPO# sheet and assign the associated Customer PO.

I've attached a sample workbook to make it simpler to understand.

I would appreciate any assistance, as this one has me a bit stymied.

Is it possible to start with a date in one column (like 1/23/2012), and get it to split into three separate columns for month, day, and year (ie. column A = month, B = day, C= year)? Seems odd but I have a large file that needs to be in that format. Thanks in advance!

I have a column of dates that are populated with xx/xx/yyyy and I would like
have another column that takes the date of the particular month & year and
combine it to be recognized only as a month-year data cell. I am trying to
do this so my pivot table will combine all of the dates within a certain
month into a common total cell.

Hi

Anybody have an answer to a problem I don't see any solution ? I have a
spreadsheet with 25,000+ lines. Within the line one column is a date of
birth in the format 04/04/1954.............is there any way to "split"
this date into three seperate columns (Day) (Month)
(Year)............what we want to be able to do is address those on the
list on the month of their birth. Grateful for any help

MNW

I have a month, year, hour, and day of month and want to convert it into one. Any help would be appreciated

Example

Year Month Day Hour
2005 12 16 2

= 12/16/2005 01:00

Thanks in advance

Good day and Happy New Year, I have Excel 2007 at home and 2003 at work. I have a question, I am a case manager and would like to learn a formula(s) that will allow me to list my caseload of names into excel while also listing their days, months, and years in the program. I would like to have a database of names with their arrival dates in the program and have their remaining dates in days, months, and years. Example John Smith, arrival date (I submit 1/1/12), days total (1 days), months total (less than a month), years total (less than a year). Please assist if you can. Thank you

I have been sent a spreadsheet with birth date day, month and year in separate columns. Does anyone know how to combine them into one date column?

Cheers,

Dave

I have a column of dates that are populated with xx/xx/yyyy and I would like
have another column that takes the date of the particular month & year and
combine it to be recognized only as a month-year data cell. I am trying to
do this so my pivot table will combine all of the dates within a certain
month into a common total cell.

hi guys

i'm thinking of letting users to input a date by using 3 textboxes for Day, Month and Year each.

i'm using something like:
dayMe.Value = Format(Now, "dd")
monthMe.Value = Format(Now, "mmm")
yearMe.Value = Format(Now, "yyyy")

problem is : how do i concatenate them togehter to form dd-mmm-yyyy again?

can it be done?

thanks again!

cheers
xlite:P

Hats off to all the kind members of this forum. This is a very helpful forum. I am overwhelmed by the willingness of the members to help.

I am very new to Excel and I may not be using the proper technical terms in my question. I am trying to explain in layman terms.

I want to create a monthly timesheet which contains 9 columns for
(Date, Day, Project no., Activity, Time In, Time Out, Total Hours, OT Hours, Remarks)

I have used IF Function to calculate Total Hours & OT hours automatically. Time IN & Time OUT, Project No., will be entered manually on daily basis.

Weekday function is used in the Day column to return the corresponding day of the date in the Date column.

Name of the Month and Year will be manually entered in the designated cells I3 and I4 respectively.

Now the solution I am looking for is, the dates should be automatically entered in the Date column (in cells A8 to A38) based on the Month & Year entered in cells I3 & I4. Dates of the corresponding month of the year should only be filled in. (If a month is not having 29, 30 or 31st day, the corresponding cells should be left blank. i.e. nothing should be displayed in the corresponding cells). I am looking for some sort of formula to enter in the cells of Date column (A8 to A38) achieve this. I have searched the forum and could not find anything which could at least give me an idea about the kind of function or formula to be used.

Attached here is the time sheet I am trying to create.

Thanks to all.

I have a column with over 11(K) date entries. This column grows daily. I
need a formula that will total the number of entries by month & year
(disregarding the day).

Hi All,

I am having a little bit of trouble when inputting dates into my sheets on Excel 2007. Whenever I type a date in as, for example, May 15 (which every other copy of excel I have used would automatically recognize as May 15, [current year]), my computer is converting to 01 May 2015.

If I click on the cell to display the contents in the input bar, it reads as 01/05/2015. If I manually type the date into the cell as May 15, 11 it will format it correctly to a 2011 date.

How can I correct my settings so that if I type a date in as "May 15" it will recognize it as "May 15, 2011" (I know how to change the formatting of the cell, its the fact that its not inputting as im used to that I am unsure of).

Thanks all!


No luck finding an answer? You could always try Google.