converting date format

  • Hi,

    I have date written in form of 25.12.2016 in excel cells. I would like to reference to this date in formula, but the formula DATE requires that date is written as =DATE(2016;12;25). So I cannot just link to that cell e.g. =DATE (B1), and would need to somehow convert date format.
    Is this possible to do it automatically or over some formula?

    I want to count in here


  • Re: converting date format

    If I have both my "b1" and "e:e" ranges formated as 'short date', your countifs works for me. Counts all dates that are equal to "b1" date...
    Why you need a date() formula inside countifs() ? Am I missing something??

  • Re: converting date format

    What do you mean by "formated as short date"?
    In column E (from row 1 to row 1128) I have many different dates. I want to count number of cells where dates are between two specific dates. e.g. in column E are dates (12.12.2016; 14.12.2016; 20.12.2016; 21.12.2016) and I need to count number of dates between 12.12.2016 and 20.12.2016.

    Since I will have to count for many different periods, that is why I would like to use reference to ranges - hence B1 and B2 (this was mistake in example in first post - it should be B2 in second part of formula (I have corrected it now).
    But the formula I have requires date to be in special format ( =date(yyyy;mm;dd) and I have it in different ( and the formula is not working when I try to enter link to cell with date (B1 and B2).

  • Re: converting date format

    I tried it again, and now it is working. No idea what was wrong last time.
    Thanks for your help and effort :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!