Forum Discussion

christ7's avatar
christ7
Copper Contributor
May 06, 2019

Date Values and Event Sequencing

Hello,

I've inherited a spreadsheet to manage fitness data and there appears to be errors in some cells but not in others.

In the picture attached:

- There is no date value in cell H2 but there is in H338 - yet the formula appears to be the same and the date format is the same.

- There is also no trial ID in cell G2 but there is in G338 - yet, the formula appears to be the same (and is based off date value cell).

- The trial number formula (F2) has issues and is based from the trial_ID values.

 



Any help is appreciated.

File is attached.

Chris

excel
Formulas and Functions
power bi

7 Replies

  • Rich99's avatar
    Rich99
    Iron Contributor
    May 06, 2019

    christ7 Hi, I believe it all has to do with the format of the date in the "Test Date" column. You will notice that error only occurs when the day is greater than 13 for example 13/Sep/2017. if you change the date to 12/Sep/2017 then your formula will work. 

     

    If you change the formula in the "Date Value" column to be DAY, MONTH, YEAR instead of MONTH, DAY, YEAR then the formula will work, although I haven't checked that you get the correct answer. 

    • christ7's avatar
      christ7
      Copper Contributor
      May 08, 2019

      Rich99 

      Hi Rich,

      Thank you for the help with the date values etc.

      Yet, in column F (TrialNumber) there's a change in values in some rows even though previous ones are correct (top of the document). This is fitness data where players will do 3 vertical jumps, so the trials should be 1-3. Yet, some players have 4-6 for their TrialNumber, whereas others have 2, 3, and 5. The order goes wrong and random for some players......

      Any ideas why this might be? Or any alternative solution?

      I've attached the file in here.

      Any help is appreciated.

      Thank you

      Chris

      • Rich99's avatar
        Rich99
        Iron Contributor
        May 08, 2019

        christ7 

        Hi Chris, 

        What I did do is copy rows 3506 to 3508 and paste them into a separate spreadsheet (see attached) to make it easier to work out what was happening and as you can see in column F, I came up with totally different figures to what's in your spreadsheet. I am not sure quite whats happening so feel I've got about as far as I am likely to get. Hope this helps and you manage to sort it.

         

        I'll have another look tomorrow but not expecting to understand it better, but you never know

         

        Rich99

Resources