• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Formula

J

JEC8878

New Member
I am calculating care visits and whether these were early,late or missed.
I have Planned start times and actual start times and need to calculate if the actual start time is more/less than planned by 30m then this will show early/late but then if the actual time is blank then this will show missed
 
What version of excel are you using - show version as solutions will be dependant on what version you have and therefore what functions available.
Excel Versions are: 2003/2007/2010/2013/2016/2019/2021/2024 (due April24) /365 subscription , also rather than show more than 1 version . if you have them , show which version you will be using the solution for.
An awful lot of new functions have now been added to the newer versions

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

OR( actual time < cell with planned time - timevalue("0:30:0"),actual time > cell with planned time + timevalue("0:30:0"), cell with actual time ="")
in an IF and also another cell to see no data entered would help
 
ok

OR( actual time < cell with planned time - timevalue("0:30:0"),actual time > cell with planned time + timevalue("0:30:0"), cell with actual time ="")
in an IF and also another cell to see no data entered would help
 
Thank you - this is bringing up a True/False response - im needing Early - if actual is more than 30m before planned - Late - if actual is more than 30m after planned or missed if the actual time cell is blank
 
yes it will , as i say needed to ad to an IF
in an IF and also another cell to see no data entered would help
Click to expand...

=IF(actual time < cell with planned time - timevalue("0:30:0"), "Early", IF ( OR(actual time > cell with planned time + timevalue("0:30:0"), cell with actual time ="") "Late", ""))
But you need something on blank rows to ou can copy down to rows not yet used
otherwise it will report Late for blank rows

may need to add a blank test
=IF(AND( actual time <>'', actual time < cell with planned time - timevalue("0:30:0")), "Early", IF ( OR(actual time > cell with planned time + timevalue("0:30:0"), cell with actual time ="") "Late", ""))
or change the order for the test to late first then early
 
=IF(AND( actual time <>'', actual time < cell with planned time - timevalue("0:30:0")), "Early", IF ( OR(actual time > cell with planned time + timevalue("0:30:0"), cell with actual time ="") "Late", ""))

It is showing the orange commas as an error
 
should be ""

how about a sample sheet - would have been much quicker as i would have also tested the syntax in excel
 
=IF(OR(G2>(D2 + TIMEVALUE("0:30:0")), G2=""), "Late", IF(G2<(D2 - TIMEVALUE("0:30:0")), "Early",""))
BUT where did MISSED come from
I'm assuming if blank , then missed
so
=IF(A2="","",IF(G2="","Missed",IF(G2>(D2+TIMEVALUE("0:30:0")),"Late",IF(G2<(D2-TIMEVALUE("0:30:0")),"Early",""))))

and testing A2 means the formula can be copied down regardless of entries in the rows
 

Attachments

  • Sample-ETAF.xlsx
    12 KB · Views: 9
You must log in or register to reply here.
Back
Top