none
Excel Frequency function won't count dates coming from VBA calendar RRS feed

  • Question

  • Hi,

    When using the Frequency function it only count dates that I'm typing manually but not from the VBA calender.
    In the file I have uploaded in sheet"day" I fill for example some data.

    You can see a VBA in c2:d3 when pressing it a calander apper, when chosing date it will update cell b1 (linkedCell).

    Then when pressing the VBA "Copy to month" all the data copy to the "month" sheet.

    And in the "list" Sheet there are the totales for all the employee data/info from the "month" sheet.

    The problem is with the Frequency function in cells J2:J14 and cells I16:I20 in the "list" sheet.

    It only count the dates from the "month" sheet if I only typing the dates manually in cell b1 at the "day" sheet and then pressing the ENTER on the keyborad

    Sunday, December 21, 2014 1:56 AM

All replies

  • Hi Palermo,

    Thanks for posting in MSDN forum.

    >>Then when pressing the VBA "Copy to month" all the data copy to the "month" sheet.<<

    >>It only count the dates from the "month" sheet if I only typing the dates manually in cell b1 at the "day" sheet and then pressing the ENTER on the keyborad<<

    Did you get any error message? Is there any difference between the format of date type manually and from the calendar?

    Also I didn't see the sheet you uploaded, would you mind share a demo worksheet to help us to reproduce the issue?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 22, 2014 7:34 AM
    Moderator
  • Did you get any error message? Is there any difference between the format of date type manually and from the calendar?

    No error message and there is no difference

    Also I didn't see the sheet you uploaded, would you mind share a demo worksheet to help us to reproduce the issue?

    I upload the link:https://drive.google.com/file/d/0B069R4rVCHQ7Z25taHIwMzdIeXM/view?usp=sharing


    • Edited by palermo8 Monday, December 22, 2014 1:41 PM
    Monday, December 22, 2014 1:38 PM
  • Hi,

    >> Excel Frequency function won't count dates coming from VBA calendar

    According to my investigation, the value returned from the calendar box is “text” rather than date value.

    So when you tried to calculate the frequency by using the text value, it would not be calculated correctly.

    I changed the formula:

    IF(A7="","",IF($B$1="","",$B$1)) -> IF(A7="","",IF($B$1="","",DATEVALUE($B$1)))

    I don’t quite understand the formula:

    COUNT(1/FREQUENCY(IF(month!$A$2:$A$2000=LIST!$A2,month!$B$2:$B$2000),month!$B$2:$B$2000))

    So I tested the FREQUENCY function by using the formula as following, and it worked as expected.

    FREQUENCY(B6:B2000, N13:N14)

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, December 24, 2014 3:19 AM
    Moderator
  • Hi,

    Changing the formula in cell b7 (sheet "day")

    from :IF(A7="","",IF($B$1="","",$B$1)) to -> IF(A7="","",IF($B$1="","",DATEVALUE($B$1)))

    is giving me #VALUE!

    I will try to explain again when entering the date from the vba calender the FREQUENCY fromula wont count it (sheet "list") cells J2:J14 and cells I16:I20.

    But if i'm entering the date manually and in cell b1 (sheet "day") it all good.

    the FREQUENCY fromula in (sheet "list") cells J2:J14 and cells I16:I20 is for counting count dates for employee and ignoring duplicates

    Wednesday, December 24, 2014 8:09 AM