Teach Yourself Excel Lesson Dates - Dates

I have a number of workbooks that recorded the date each line item (row) completed each step (column: left to right, fill in the date as you go), along with some 'N/A' data. I can provide a generic sample, but do not have permissions to post attachments to this forum.

I am trying to plot a curve depicting the completion rate of all line items, with the calendar (date range) on the x-axis, and the number completed on the y-axis.

Hi. I am creating a Staff Leave Roster and would like to highlight dates on a calendar based on dates entered into rows in excel. Currently i am using a "From" and "To" column for when an individual will be on leave. For example, if i will be taking leave from Monday, January 10, 2011 To Monday, January 24, 2011
I would like this time period to be reflected on the calendar.

There are often requirements to generate Excel reports from Database applications.

In such scenarios, I often generate valid Html from the data, and export it with a .xls extension relying on Excel capabilities to process Html.

In such recent scenario, I was preparing a report, which had the following in it:

<td>06-07</td>

Excel automatically rendered it as 7 Jun. Clearly this is not the desired behavior. I wrapped it as:

<td><![CDATA[06-07]]></td>

This time Excel showed nothing in the cell. I even tried wrapping it in <pre>, but Excel now simply used a different font, but still rendered it as 7 Jun.

Is there any way to specify in Html that Excel should display the data as is without applying any transformations whatsoever??

Hello,

I have imported data in Excel. My data looks as date format: 31-May-2010 but if I right click on the cell and check the format it shows that it is general format.

I have at about 1000 lines in my database and if I select the column and try to change the format, only at about 20 percent of my cells really change. I tried everything possible but without success.

I have Excel 2003 at work and the format changes Excel 2007 at home and it don`t want to change.

Thank you

Lubi

I am receiving excel models from a European colleague. The models contain many date fields and many formulas based on these dates (vlookups, sumifs, ifs, etc.). However, when I open the file many of these formula cells are #VALUE! - I believe because the dates they reference are in European format ("24.04.09" for April 24, 2009) and thus my US version of excel does not recognize them as dates.

Is it possible for me to change an excel or computer setting so my US version of excel 2007 can recognize these as dates automatically (i.e. without going through the model and manually converting the dates myself). The models are huge with dates scattered throughout, so finding and converting all dates to US format manually would take me days.

Thanks in advance for any help!

I have a workbook with dates that are formatted like this: MM/DD/YYYY HH:MM. I need to use the sum function to calculate duration of two date columns. So far I have separated them into separate files used the left/mid/right functions, but now they are in true date format, formatting back to a normal date does not affect them. Anyone know how to convert true dates into regular US dates?

i have one column(A) that includes all dates from june 2003 till june 2008 (but only weekdays are included).

i have another column(C) that includes most of these dates (but some days are missing), and then beside this column is another one (D) with the rates corresponding to those dates in column C.

what i want to do is, for each missing date (weekdays only), i want to take an average of the rate on the day before and the rate the day after.

is there a quick way to do this? right now, ive just been matching each date on column A with column C, returning a 1 if theyre the same date and a 0 otherwise. So when I see a 1, I copy over the rates corresponding to those rows in D over to my new column. and whenever i see a 0, i calculate the average and then move the rest of Column C and D down one.

its not so much the average calulations that take a while, its this whole process of finding a 0 and then each time i do, i have to move the rest of the columns down one and then i have to change my match formula. any ideas of a quicker way to do this? thanks!

Hello everyone,

This is what I am trying to accomplish. I want to be able to enter a date or range of dates ie...3/17/2011 or 3/1/2011-3/17/2011 in a cell and then have those dates change color on a calendar that is on the same sheet or another tab or tabs. Hope I explained myself well enough and thanks for any help in advance becuase I am not very good with this type of stuff.

Hi there,

I need to convert the following string into Excel dates:

Jan 1, 2009
Jan 12, 2009
Jan 21, 2009

Right clicking it and changing it to date format will not work.

I have tried extracting individual days/months/years out by using Left/MID and Right functions but as you can see, problem lies when there days with single digit or double digits.

Does anyone have a better solution?

Thanks

Hi Excel Experts,

I have a problem that i cant seem to figure out.

I have a Huge spreadsheet with start and end dates in column A & B respectively.

cell D1 to Z1 I have Weekending Dates Starting from Weekending 5/Feb/10

My problem is when the user enters in a start date of say 1/feb/2010 and an end date of 10/feb/10 in cells A2 and B2 respectively, i want a formula in cells D2 to Z2 that will calculate How many business days of this Start and End Date Fall within the weekending date. This then needs to be dragged down..

So in the example above, if the start date is 1/Feb/2010 and an end date is 10/feb/10 in cells A2 and B2. Than in Cells D2 which has the Weekending Title of 5/Feb/10 should auto calculate the result of 5. Then in cell E2 which has the weekending title of 12/Feb/10 should auto calculate the result of 3. Because there are no business days for F2 which has the weekending title of 19/Feb/10, that are triggered by the start and end date above, than this should auto calculate 0.

And so forth..

I tried everything i know but i cant get this to work..

Pleeeassee help meeeeee..

thankyou soooooo much everyone.. I look forward to your formula solutions