Free Microsoft Excel 2013 Quick Reference

Combining Day + Month + Year from columns to make a Date?

Another brain teaser for the Novice :o

How do I combine a Day in Column A with a Month in Column B and a Year in Column C to create a Date (formated as a Date and not Text)?

Dates.... argggh must be a childhood curse


Post your answer or comment

comments powered by Disqus
Hi Guys,
Please can you help me.
I want to take a number from column: A and column: B and in Column:C make a month & Year.

Column: A = has numbers representing month : ie ( 1, 5 ,12, 8 etc )
Column: B = Has numbers representing Years : ie ( 2005, 2004, 2006,2007 etc )

In column C I need a formula to combine those two figures to give me a = Month & Year ie ( July 2006, April 200& etc )

Thanks for your time

How do you make a date work, when it is made up from 3 different cells?
ie: April in A1, 2010 in B1, and 1 in A4.
How do I make this into April 1 2010, in such a way, that I can use the result in subsequent formulas?
I have included a sample workbook. Cell A6 is what I am hoping to end up with.
FYI.. I am wanting to copy the formula down thru all the days of the month.
Thanks for any help

Hello, I have a spreadsheet that has a Preferred Day of the Week column and a date range column from which to select a date to conduct inventories. Is there a formula that will automatically select the best date for an inventory based on the required date range AND a specific day of the week. For example, if one store must be inventoried on a Thursday between 5/5 and 5/13, is there a formula that will select 5/10 so that I don't have to manually type that date in my spreadsheet?

Hi,

I have a project I'm working on for my job. In one column (H), I have a list of dates and in the next column (I), I need it to show that precise same date three years later to the day. I.e., if column H has a date of 6/26/2005, I need column I to say 6/26/2008.

I need to do this for approximately 800 rows and obviously, I'd rather not do it one by one. Is there any way to tell column I to add three years to column H's date? I tried a couple of things, but none of them worked.

Ok so I'm new to this site but I really was looking for some help from you pro's on excel! I was wondering if there is any way to keep a document with a bunch of recipes in it and when I wanted to make a shopping list then I could chose specific recipes and have it calculate a shopping list for me. Also if that is possible would it add up the totals of the ingredients. For example if I had chosen 2 recipes and one had 1 can of black beans and another had 2 cans of black beans would it be able to calculate 3 cans of black beans on the shopping list??? I know I'm probably the only person on this site wondering this but I figured you guys would be the only ones to know how to do this. Do you know any way of doing this?? Please help!!!

I have 3 seperate cells each with a number, for example, D2 has number 4, E2 has number 21 and F2 has number 2009. I need to combine these 3 cells to make the date 4/21/2009. I know this can't be as hard as I am making it out to be.

Does anyone have any ideas? Thanks for your help.

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

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

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.

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've got 3 columns- the first is a number for the month, the 2nd with the day, the 3rd with a year. I need to Concatenate the 4th column so it reads as a date using the first 3 columns

Example:

mm dd yy DATE
05 30 76 05/30/76

Thanks!!!

ACH in Chicago

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

I have two columns of numbers. The first is the area code and second is the phone number.

How can I combine the two columns to make one with both: as in area code-phone number?

As always, thanks for your help.

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.

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

Hi there.
I am using this formula

=IF($A5="Select","",MAX(INDIRECT(SUBSTITUTE($A5,"/","")&"m1440"&"!d:d")))

It looks for the sheet based on A5
It finds the max number from the sheet in Column D

This is what i would like to do please....

On this found sheet - Column A has dates in this format yyyy.mm.dd

I would like to enter a date in a cell (say for example AA5 on main sheet)

Can the formula above be adapted to:

Look for sheet based on A5
then
Look at date entered in AA5
then
use Vllookup to find row containing date in AA5 (making table range the ENTIRE SHEET found based on A5)
then
Return value from Column D

Thanks in advance... Hope this is possible...

I need to count cells of various months, but the cells autopopulate from the database with date and time. (i.e., 6/26/2008 1:16:00 PM)

Can I "convert" or "extract" the month and year from each cell via a formula so that only the month and year remain?

Note: Changing the format of the cell only changes the view rather than the data content.

Many thanks.

I have three columns of data: first name, middle name, last name. I want to
make a fourth column that contains the first letter of the first and middle
name and the entire last name to make a fourth column. example: john edward
smith in the first column and the fourth column would be jesmith.

thanks in advance.
--
Jerry

I am looking for a way to pull column "V" which are dates from different sheets of a workbook to make a calendar. I have jobs that are shipping on dates which are entered into column V. I want that info to automatically be moved onto a calendar which would be a different workbook. Does anyone have a way to help with this? I don't know much about macros but can follow directions well. Thanks in advance.

hi
I recently installed 'windows 7 ultimates' , and after i insalled Excel 2007 .. the columns start from right to lfet (..., C, B, A), I want to reverse it to start from left to right (A, B, C, ...),
I don't know whether the new ystem 'windows 7 ultimates' has some effect on it to be modified like this. moreover, the language of system is English ..
so how to solve it

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 haven't figured out how to take a specific range of data from a worksheet
and use this range, say 7 or 30 days to retrieive data associated with this
range to make a chart.
I would like this to be automated depending upon the value of days, again 7
or 30 calendar days.


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