Non consistency with displaying dates

[AnniekBos]

Dears,

I have a file in which I need to calculate the number of days per year between two given dates.
For example: 10/01/2014 - 05/03/2014. 10 jan 2014 - 5 march 2014.
. When I stand on the cell, the date displaying above, where the selection shows, its written: 01/10/2014 , 1 october 2014.

I found out that the cells are set as custom instead of data. When I adjust this, the selection line above adjusts, instead of the cell itself.
Is there a way to adjust the the cells in the column the right way all that once instead of one by one manually? Its a very large document and would take too much time.

And a second problem that ive come across.
You have helped me before with the formula for calculating the number of days per year between two given dates.
Ive applied it and it works most of the time. There are several lines where the formula is somehow not working. To show you:

2009 2010 etc
17/03/2015 01/12/2015 365 365 365 366 365 365 335 0 0

Formula: =MAX(0,MIN($E10,DATE(F$1,12,31))-MAX($D10,DATE(F$1,1,1))+1)

Here it does work:
25/08/2013 23/01/2014 0 0 0 0 129 23 0 0 0

Formula: =MAX(0,MIN($E6,DATE(F$1,12,31))-MAX($D6,DATE(F$1,1,1))+1)

Thanks so much for your help!

Anniek

[shukla.ankur281190]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AnniekBos]

Thanks for your reply.
Ive attached a sample sheet for you to see.
1-9 are correct. Formulas and cell displaying all as it should be.

then the 3 lines below are examples of where it goes wrong, in orange.
The dates display differently in the cells and above, in the selected line. Whatever I enter in the cell itself, i want it to display the same above, in the european way.
And i guess the counting of the days speak for itself.

Thanks you.

[shukla.ankur281190]

I think this format issue you can try
if there is format use then you can change with find and replace option Press control+H put the find value and replace with value and click on replace all and the ater look formula should work.



Otherwise May be try

F2

Please Login or Register  to view this content.

[AnniekBos]

Im sorry but its not clear to me which issue you are talking about. The first or second from my email?
im also not sure how you want me to use that formula.

Is there a way that you know of that can help me with the date jumping?

Thank you.

[shukla.ankur281190]

Use the formula in F2 as suggested in post


Check the attached file.

[JohnTopley]

See attached: dates in orange are formatted as US dates (corrected)

and dates in D "appear" as though formatted as text. Re-entering corrects error
r.

[AnniekBos]

Hi John,

Yes i know that manually this can be fixed. Its just that its a very large file and it would take a lot of time to check them all one by one.
I do see you managed to get the formula right, was there a mistake or something I did when dragging it down?
I'd like to know if i did something wrong, or it was a mistake in the formula. So I know what to look for when entering more data.

Many thanks!

[JohnTopley]

I did not change the formula: it was simply the "MAX($D10,DATE(F$1,1,1))+1)" part of the formula was not calculating correctly due to the dates in column D.

I am not sure why some dates are "wrong".

You could try selecting column D, then "Date" ==>"Text-to-Columns"==>Step 3 => select Data (radio button)==>DMY and "Finish"

[AnniekBos]

Ok I got it, this is helpful, thanks!