Microsoft Office Forums Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 10-28-2018, 05:12 AM
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 300
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

This is a whole lot more than just adding coloured triangles on cells - font colours and sizes too.
I see there are formulae in many ofthe cells; presumably you are asking users to overwrite formulae. If that's the case, can we assume that only cells without formulae need to be considered for triangle addition? Can we also assume that all cells which still have a formula in them need to be white font? If that is so, does that font need to be white before the teacher is given the sheet for filling in?
Reply With Quote
  #17  
Old 10-28-2018, 06:30 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Competent Performer
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 221
Marcia is on a distinguished road
Default

You assumed correct that:

1. the users overwrite the formulae when and if a pupil is absent, tardy or has skipped classes. The triangles will then be applied in these manual entries.
2. the cells where the formulae remain intact will be in white font.
3. the font must be in black before the user fills up the form so that he/she can determine which cells the combination codes XTI, XTIX, XLE, XLEX, LTI, LLTI, CLE, CTI will apply to.


I have attached a sample sheet of a hard copy of the form after the codes, font sizes and font colors were applied.
Attached Files
File Type: xlsm JUNE 2018 QUERYB.xlsm (117.1 KB, 3 views)

Last edited by Pecoflyer; 10-30-2018 at 04:23 AM. Reason: Removed quote
Reply With Quote
  #18  
Old 10-29-2018, 07:50 AM
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 300
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

This is not easy.
The following are questions only about user input when they overwrite formulae:
1. Can you confirm that the codes you mention are the ONLY codes a user will input:
XTI, XTIX, XLE, XLEX, LTI, LLTI, CLE, CT along with LLE, LLLE, C, CTI which you mention elsewhere
plus:
'X' absent am
'XX' absent all day
'XL' absent am, late pm
'L' late am
'LL' late am and late pm
'LX' late am, absent pm

plus:
(I proposee that anything after a space or spaces pertains to the afternoon, even when that space is the first character)
PHP Code:
'X X','X  X','X   X','X    X'even 'X           X' all mean absent all day.
' X','  X','   X','    X','          X' all mean absent in the afternoon only.
'L L','L  L','L   L','L    L','L           L' all mean late/tardy am and pm.
' L','  L','   L','    L','          L' all mean late/tardy in the afternoon only.
'X L','X  L','X   L','X    L','X           L' all mean absent am and late pm
'L X','L  X','L   X','L    X','L           X' all mean late am and absent pm
(It's not PHP code; it was a way to prevent this site removing multiple spaces from my text.)



I notice that some single X characters and some single L characters in your sample sheet are right aligned by cell formatting to signify pm. This is not reliable (and you'll have people trying to right align one character, and left align another within the same cell!). I'd recommend all cells to be centre aligned and people use a space or spaces to distinguish am from pm.

This leaves a few questons:
2. C for cutting classes (what does this mean?) does not have an attachment to am/pm?
3. Are other combinations possible? Eg.:
TIC
LEC
LETI
TILE
etc.etc?
Reply With Quote
  #19  
Old 10-29-2018, 09:22 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Competent Performer
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 221
Marcia is on a distinguished road
Default

Remove the CT code, it should be CTI. The rest of the codes above are okay.

Thank you for the XL and LX codes, I haven't thought of those possibilities

I agree with your suggestions re spaces, go ahead with that.
As for the C code, there's no distinction whether am or pm.
No other codes (TIC, LEC, LETI, TILE) . I am sure I have covered all the conditions.

Last edited by Pecoflyer; 10-30-2018 at 04:23 AM. Reason: Removed quote
Reply With Quote
  #20  
Old 10-29-2018, 10:38 AM
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 300
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

You have
XLEX
absent am, late enrolment,absent pm ??

You also have
LLLE
late am, late pm, late enrolment ??

then should you not also have
XXLE
and
LLEL
??




[and perhaps also XLEL,LLEX?]


or am I missing something?
Reply With Quote
  #21  
Old 10-30-2018, 02:32 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Competent Performer
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 221
Marcia is on a distinguished road
Default

XLEX = is absent am, late enrolment, absent pm
Scrap LLLE, it should be LLEL = Late am, late enrolment, late pm (consistent with XLEX code sequence)

Scrap LLTI, it should be LTIL = late am, Transfer In, late pm (consistent with XLEX code sequence)

You're right, please add:
XLEL = Absent am, late enrolment, late pm
LLEX = Late am, late enrolment, absent pm
XTIL = absent am, Transfer In, late pm
LTIX = Late am, Transfer In, absent pm
TIX = absent pm, Transfer In ( so that the X will fall on the right triangle)
LEX = absent pm, late enrolment



THANK YOU!!! There's power outage so I might not be able to respond to any further clarifications tonight.

Last edited by Marcia; 10-30-2018 at 10:56 PM. Reason: Removed quote
Reply With Quote
  #22  
Old 10-30-2018, 04:22 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 64bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@ Marcia
please do not quote entire posts needlessly. They clutter the thread and make it hard to read.
Thanks
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #23  
Old 10-30-2018, 02:14 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Competent Performer
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 221
Marcia is on a distinguished road
Default

Noted Pecoflyer. Thank you.
Reply With Quote
  #24  
Old 11-01-2018, 03:36 PM
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 300
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

I am still working on this, on and off. I should have something for you by the weekend.
Reply With Quote
  #25  
Old 11-01-2018, 03:47 PM
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 300
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

One little question. Can someone be cutting classes and be late in the afternoon on the same day?
C L, CL,CTIL etc.
I ask because you say you want cutting classes to be represented by a lower right red triangle, and you want late/tardy in the afternoon to be represented by a lower right blue triangle. Clearly these two triangles are in the same place.
Reply With Quote
  #26  
Old 11-01-2018, 03:53 PM
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 300
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by Marcia View Post
There will be a conditional formatting that will turn all the typed remarks except the remarks for absences ("X", "X X")into white font color, before printing the form, something that I learned how to do. The printed form will show only the Xes and the shades for late and cutting classes.
I'd be interested to know how you do this!


Quote:
Originally Posted by Marcia View Post
Is there a better way to shorten the very long formula in E21 to AI21?
I will look at this after we know for sure the sort of things that will appear in the cells above that formula. In the meantime, what version of Excel are you using? There are some newer functions which could simplify the formula a lot.
Reply With Quote
  #27  
Old 11-01-2018, 05:06 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Competent Performer
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 221
Marcia is on a distinguished road
Default

I am using Excel 2016. When and if a student skips classes (am) and is late (pm) on the same day, the late triangle should be placed on the left, the cutting classes shape on the right regardless of the time of misdemeanor, so only the codes LTIC, LLEC, LC should be used. The teacher will have a note somewhere in the form on additional info like the above cases. The same holds true with am cutting classes, pm absent, where the X is typed on the left, the cutting class shape placed on the right.

On the conditional formatting, I was thinking of doing the simple =E16="DRP" format color white, =E16="T/O" format color white. I was hoping that the macro will do the job of making the "LE", "T/I","TI" letters into white color font.

I didn't know that this form will turn out to be so complicated, but please don't give up on this because I know many others out there are following and monitoring the final output of this "project" given the number of views on record. Thanks.

Last edited by Marcia; 11-01-2018 at 07:15 PM.
Reply With Quote
  #28  
Old 11-02-2018, 04:43 AM
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 300
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by Marcia View Post
When and if a student skips classes (am) and is late (pm) on the same day, the late triangle should be placed on the left, the cutting classes shape on the right regardless of the time of misdemeanor, so only the codes LTIC, LLEC, LC should be used. The teacher will have a note somewhere in the form on additional info like the above cases. The same holds true with am cutting classes, pm absent, where the X is typed on the left, the cutting class shape placed on the right.

I'd be a bit wary of this plan. You have a system where the upper left half of a cell is always the morning, and the lower right always the afternoon. Now you have to make an exception if there's a red triangle: you don't know if the lateness is in the morning or not, you don't know if the cutting classes is in the morning or not, the absenteeism could be morning or afternoon. I'd strongly recommend not doing this.
There are plenty of ways to indicate things in the cells while keeping the am/pm layout correct.
It looks as if cutting classes could be split am/pm, or perhaps you're happy for this to be represented without am/pm information? Either way there are plenty of ways you might represent this in a cell - it's down to your imagination.
For example, you might use one of these to show the student has been cuttting classes sometime on a day (no am/pm nformation):




Instead, you could keep am/pm information this way:


It's fairly obvious what's happening.
Reply With Quote
  #29  
Old 11-02-2018, 05:12 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Competent Performer
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 221
Marcia is on a distinguished road
Default

I fully agree with you that it i confusing to make an exception on the representation of cutting classes, always shaded triangle on the right. It is clearly a contradiction on the rest of the coding system but it is the instruction from the higher ups. I suggest we maintain the status quo as is the instruction. Although there is plan to make representations before the approving authorities that we make our own symbol as long as the figures are correct. Could you please make the macro as flexible as possible so that in case the powers that be would approve on our proposal the codes would be easier to amend?
If you must know, the knowledge of the users of this form on the power of Excel to help them in their work is pathetically nil. In fact some of them still use crayons to fill up the form and calculators to get the totals and average daily attendance. I am optimistic that once we present to the powers that be that it is possible to reflect in the form all the conditions in the proper formats of am and pm through the use of different colors and symbols, they will look favorably on such enhanced reports.
Reply With Quote
  #30  
Old 11-02-2018, 06:23 AM
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 300
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by Marcia View Post
I suggest we maintain the status quo as is the instruction. Although there is plan to make representations before the approving authorities that we make our own symbol as long as the figures are correct. Could you please make the macro as flexible as possible so that in case the powers that be would approve on our proposal the codes would be easier to amend?
How are you going to display cutting classes am, late am, absent pm?

Besides being significantly more convoluted to code for, it'll confuse users trying to get what they want - they'll need the contradictions/exceptions explained, and it'll be more difficult to get the row and column totals correct (If the letters L, X & C in a cell all represent half days (not counting the L in the LE pair), then counting occurrences of these letters makes for simpler and more robust totals formulae.)

The 'authorities', 'powers that be', 'the higher ups', whatever, haven't put the thought into this that you (and I) have; their decisions are often ill-informed and ill-advised, sometimes they're just bloody-minded and want to show who's boss. It's up to you to advise and inform them what's best. At the moment I'm leaning strongly towards my last suggestion of the smaller triangles on top of the larger ones (If you want, we could even keep ALL cutting classes small red triangles, regardless of whether they're sittting atop another larger triangle, to maintain consistency).

I would prefer you to set things in concrete sooner rather than later - I don't want to have to revisit this too often. Remember, I'm doing this for nothing.

I think I might have to back-pedal a little on having something ready at the weekend, something's come up and it's already Friday afternoon here in the UK. Next week's very busy for me too. So you'll need patience, and I'm not giving up and I've made good progress.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
New learner need help - convert tables in word format to ppt slides. sas Word VBA 5 09-21-2017 08:33 PM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Good morning/afternoon/evening based on time dwirony Word VBA 1 01-03-2017 01:01 PM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Need conditional formatting formula namedujour Excel 1 05-27-2016 08:58 AM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Conditional Formatting Formula teza2k06 Excel 1 08-22-2014 02:49 AM
Conditional Formula UICOMP Excel 1 05-18-2012 12:59 PM


All times are GMT -7. The time now is 06:28 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft