Excel calculations on dates

Hi everyone,

I came across a little bit of an issue. I have attendance sheet that keep track of people punching in on time, missing days etc. Problem I am having with it is that points should resent a year after they got assigned. As an example if someone was late on March 1st 2023, next to their name in the column I'd put letter "M" that'd give them 0.33 points. On March 1st 2024 those points should not be counted anymore. Also, there can be more than 1 instance per day, in case someone does not punch in on time more than once - I'd put then "M M" which would add up to 0.66 points.

I've attached a sample below.

Thank you.

example.xlsx

Thank you Glenn! Quick question, what if I'd like to add more than just 1 letter to this whole process and assign different numerical values to it? Would I be able to do it using that formula?

You are right, I do apologize for that... Here are the values that I currently use.

M -> .33
L -> .33
E -> .33
A -> 1
T -> 1
N -> 1
PTO -> 0 (I'm adding it here since in the past I had an issue with PTO adding 1 point because of the letter T in it)

All of these can be inputted multiple times into a cell, it can also happen that 1 cell will have different letters. As an example if cell has letters "A M" the total for that day is 1.33 points. I want to get an overall total for the year from those letters.

Thank you again

Wow... This is it! Simple solution to my complicated problem - mainly because I cannot explain it. Thank you!!!!!!!