Excel attendance patterns

M

mematedavev

New Member
Joined
Jul 2, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have attached an example of the attendance spreadsheet I am using. I want to create a formula that tells me how many days since the most recent specific attendance code. All present codes are marked as / but any unauthorised days are O, Medical is M, authorised is C etc. I want to create a formula that tells me how many days it has been since a O/C/M code.
 

Attachments

  • Example 1.png
    Example 1.png
    58.2 KB · Views: 11

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Welcome. If you use XL2BB to illustrate your example and indicate what you want as expected value(s) and where, you'll have a better chance of someone figuring this out for you.
Difficult to recreate from a picture!
 
Upvote 0
You need to select a range of data, not a single cell before you copy it using XL2BB
 
Upvote 0
Sorry, not used to using this. Here you go:

Example 1.xlsx
ABCDEFGHIJKLMNOPQRSTU
1StudentYear GroupRegistration FormAM: 29 Jun 2023PM: 29 Jun 2023AM: 19 Jun 2023PM: 19 Jun 2023AM: 20 Jun 2023PM: 20 Jun 2023AM: 21 Jun 2023PM: 21 Jun 2023AM: 22 Jun 2023PM: 22 Jun 2023AM: 23 Jun 2023PM: 23 Jun 2023AM: 26 Jun 2023PM: 26 Jun 2023AM: 27 Jun 2023PM: 27 Jun 2023AM: 28 Jun 2023PM: 28 Jun 2023
21Year 7A/\OO/\/\/\/\/\/M/\
32Year 7B/\/\/\/\/\/\/\/\/\
43Year 7C/\/\/\/\/\/\/\/\/\
54Year 7DII/\/\/\/\/\/\/\/\
65Year 7E/\/\/\/\/\/\/\/\RR
76Year 7FL\/\/\VVL\/\/\/\/\
Report Data
 
Upvote 0
It's still unclear (at least to me) what you're trying to determine. Could you provide expected results from your sample?
Also, is there any significance to a "/" versus a "\" versus a "|"?
 
Upvote 0
/ is AM attendance and \ is PM. I want a formula to calculate how many days since a mark that isn’t / or \ e.g. O/M etc. I want it to look back at the attendance in the row and count how many days consecutive attendance there is since a day off
 
Upvote 0
As I said, it would be helpful to know your expected results from the sample you showed.
 
Upvote 0
Like this on the final column:

Example 1.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1StudentYear GroupRegistration FormAM: 29 Jun 2023PM: 29 Jun 2023AM: 19 Jun 2023PM: 19 Jun 2023AM: 20 Jun 2023PM: 20 Jun 2023AM: 21 Jun 2023PM: 21 Jun 2023AM: 22 Jun 2023PM: 22 Jun 2023AM: 23 Jun 2023PM: 23 Jun 2023AM: 26 Jun 2023PM: 26 Jun 2023AM: 27 Jun 2023PM: 27 Jun 2023AM: 28 Jun 2023PM: 28 Jun 2023Days since last absence
21Year 7A/\OO/\/\/\/\/\/M/\1 day
32Year 7B/\/\/\/\/\/\/\/\/\9 days
43Year 7C/\/\/\/\/\/\/\/\/\9 days
54Year 7DII/\/\/\/\/\/\/\/\8 days
65Year 7E/\/\/\/\/\/\/\/\RR0 days
76Year 7FL\/\/\VVL\/\/\/\/\5 days
Report Data
 
Upvote 0
It would help (me) if you were specific how you expect to calculate those days in the V column.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Incrementing a calculated cell by if it meets a set of criteria
Replies
4
Views
308
furness121
F
K
  • Question
Help with a formula please
Replies
2
Views
272
kralin
K
J
  • Solved
Formula for attendance daily summary
Replies
6
Views
383
kevin9999
K
S
  • Question
Adding a key (So if someone clicks "P" in the excel, it will automatically turn that cell Purple with the letter P in it)
Replies
6
Views
485
sheri54371
S
M
  • Question
Dynamic calculations based on Dates
Replies
1
Views
215
Kaper
Kaper

Forum statistics

Threads
1,216,075
Messages
6,128,657
Members
449,462
Latest member
Chislobog

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top