Home

Date Values and Event Sequencing

%3CLINGO-SUB%20id%3D%22lingo-sub-534266%22%20slang%3D%22en-US%22%3EDate%20Values%20and%20Event%20Sequencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534266%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20inherited%20a%20spreadsheet%20to%20manage%20fitness%20data%20and%20there%20appears%20to%20be%20errors%20in%20some%20cells%20but%20not%20in%20others.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20the%20picture%20attached%3A%3CBR%20%2F%3E%3CBR%20%2F%3E-%20There%20is%20no%20date%20value%20in%20cell%20H2%20but%20there%20is%20in%20H338%20-%20yet%20the%20formula%20appears%20to%20be%20the%20same%20and%20the%20date%20format%20is%20the%20same.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E-%20There%20is%20also%20no%20trial%20ID%20in%20cell%20G2%20but%20there%20is%20in%20G338%20-%20yet%2C%20the%20formula%20appears%20to%20be%20the%20same%20(and%20is%20based%20off%20date%20value%20cell).%3CBR%20%2F%3E%3CBR%20%2F%3E-%20The%20trial%20number%20formula%20(F2)%20has%20issues%20and%20is%20based%20from%20the%20trial_ID%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20856px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112259i677C457D145F8BB0%2Fimage-dimensions%2F856x545%3Fv%3D1.0%22%20width%3D%22856%22%20height%3D%22545%22%20alt%3D%22Excel%20Issues.png%22%20title%3D%22Excel%20Issues.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20help%20is%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EFile%20is%20attached.%3CBR%20%2F%3E%3CBR%20%2F%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-534266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534830%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Values%20and%20Event%20Sequencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334405%22%20target%3D%22_blank%22%3E%40christ7%3C%2FA%3E%26nbsp%3BHi%2C%20I%20believe%20it%20all%20has%20to%20do%20with%20the%20format%20of%20the%20date%20in%20the%20%22Test%20Date%22%20column.%20You%20will%20notice%20that%20error%20only%20occurs%20when%20the%20day%20is%20greater%20than%2013%20for%20example%2013%2FSep%2F2017.%20if%20you%20change%20the%20date%20to%2012%2FSep%2F2017%20then%20your%20formula%20will%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20change%20the%20formula%20in%20the%20%22Date%20Value%22%20column%20to%20be%20DAY%2C%20MONTH%2C%20YEAR%20instead%20of%20MONTH%2C%20DAY%2C%20YEAR%20then%20the%20formula%20will%20work%2C%20although%20I%20haven't%20checked%20that%20you%20get%20the%20correct%20answer.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-539488%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Values%20and%20Event%20Sequencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539488%22%20slang%3D%22en-US%22%3EIt%20worked!%20Thank%20you%20for%20the%20help%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F259521%22%20target%3D%22_blank%22%3E%40Rich99%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-548256%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Values%20and%20Event%20Sequencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F259521%22%20target%3D%22_blank%22%3E%40Rich99%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHi%20Rich%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20the%20help%20with%20the%20date%20values%20etc.%3CBR%20%2F%3E%3CBR%20%2F%3EYet%2C%20in%20column%20F%20(TrialNumber)%20there's%20a%20change%20in%20values%20in%20some%20rows%20even%20though%20previous%20ones%20are%20correct%20(top%20of%20the%20document).%20This%20is%20fitness%20data%20where%20players%20will%20do%203%20vertical%20jumps%2C%20so%20the%20trials%20should%20be%201-3.%20Yet%2C%20some%20players%20have%204-6%20for%20their%20TrialNumber%2C%20whereas%20others%20have%202%2C%203%2C%20and%205.%20The%20order%20goes%20wrong%20and%20random%20for%20some%20players......%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20ideas%20why%20this%20might%20be%3F%20Or%20any%20alternative%20solution%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20attached%20the%20file%20in%20here.%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20help%20is%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%3CBR%20%2F%3E%3CBR%20%2F%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-548775%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Values%20and%20Event%20Sequencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334405%22%20target%3D%22_blank%22%3E%40christ7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Chris%2C%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20did%20do%20is%20copy%20rows%203506%20to%203508%20and%20paste%20them%20into%20a%20separate%20spreadsheet%20(see%20attached)%20to%20make%20it%20easier%20to%20work%20out%20what%20was%20happening%20and%20as%20you%20can%20see%20in%20column%20F%2C%20I%20came%20up%20with%20totally%20different%20figures%20to%20what's%20in%20your%20spreadsheet.%20I%20am%20not%20sure%20quite%20whats%20happening%20so%20feel%20I've%20got%20about%20as%20far%20as%20I%20am%20likely%20to%20get.%20Hope%20this%20helps%20and%20you%20manage%20to%20sort%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20have%20another%20look%20tomorrow%20but%20not%20expecting%20to%20understand%20it%20better%2C%20but%20you%20never%20know%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERich99%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-548777%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Values%20and%20Event%20Sequencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334405%22%20target%3D%22_blank%22%3E%40christ7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Chris%2C%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20did%20do%20is%20copy%20rows%203506%20to%203508%20and%20paste%20them%20into%20a%20separate%20spreadsheet%20(see%20attached)%20to%20make%20it%20easier%20to%20work%20out%20what%20was%20happening%20and%20as%20you%20can%20see%20in%20column%20F%2C%20I%20came%20up%20with%20totally%20different%20figures%20to%20what's%20in%20your%20spreadsheet.%20I%20am%20not%20sure%20quite%20whats%20happening%20so%20feel%20I've%20got%20about%20as%20far%20as%20I%20am%20likely%20to%20get.%20Hope%20this%20helps%20and%20you%20manage%20to%20sort%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20have%20another%20look%20tomorrow%20but%20not%20expecting%20to%20understand%20it%20better%2C%20but%20you%20never%20know%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERich99%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-551448%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Values%20and%20Event%20Sequencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-551448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F259521%22%20target%3D%22_blank%22%3E%40Rich99%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Rich%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20this.%20As%20you%20say%2C%20the%20formula%20works%20in%20a%20separate%20sheet%20('Test%20Data'%20-%20attached)%20but%20I%20copy%20the%20formula%20into%20the%20existing%20sheet%20and%20it%20doesn't%20work.%20The%20correct%20numbers%20do%20appear%20momentarily%20when%20pasting%20the%20formula%2C%20but%20when%20it%20takes%20a%20moment%20to%20load%20they%20switch%20to%20%23VALUE!%20and%20remain%20like%20that.%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20last%20ideas%20are%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%3CBR%20%2F%3E%3CBR%20%2F%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-551449%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Values%20and%20Event%20Sequencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-551449%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F259521%22%20target%3D%22_blank%22%3E%40Rich99%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20existing%20file%20which%20the%20formula%20doesn't%20transfer%20to.%3CBR%20%2F%3E%3CBR%20%2F%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E
christ7
Occasional Contributor

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.

 

Excel Issues.png

Any help is appreciated.

File is attached.

Chris

7 Replies

@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. 

It worked! Thank you for the help @Rich99

@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

@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

@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

@Rich99 

 

Hi Rich,

Thank you for this. As you say, the formula works in a separate sheet ('Test Data' - attached) but I copy the formula into the existing sheet and it doesn't work. The correct numbers do appear momentarily when pasting the formula, but when it takes a moment to load they switch to #VALUE! and remain like that.

Any last ideas are appreciated.

Thank you

Chris

@Rich99 

 

And existing file which the formula doesn't transfer to.

Chris

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies