Reminder setting issue

[panagiotisstefanidis]

Dear all,

I am trying to set a reminder as shown in the picture for people making absences. For example, for that absent in cells D6-K6, I want a reminder in cell AO 6 that would give the sum of cell D2+15. Thus I say that if D6-K6 is greater than 0, then AO=D2+15.

Ideally, D2 should contain date and AO should sum that date plus 15, but it is not an issue right now. However, no matter what I do, i get a value issue. Only when I say that if D6>0, does it work.

Please, I need some guidance.

Thank you in advance.

[#2]

@panagiotisstefanidis 

Unfortunately an image alone doesn't really help much in the diagnosis.

A guess, however, based on your verbal description: it sounds as if some of the dates in column D may actually contain text rather than numbers formatted as dates. They would look alike to the human eye, but Excel treats them differently.

If that's not it, may I suggest you post a copy of the actual workbook, just blocking out or altering any confidential or private information.

[#3]

@mathetes 

Dear Mathetes,

attached is a copy of the workbook. What I am trying to do is if any of the cells D6-K6 or all of them have the number 1 inside, then in cell AO to make it automatically either to show number 15 or to sum the date in D2 + 15.

I am not sure if that helps. Thannks for your help anyway.

[#4]

@panagiotisstefanidis 

Well, I'm sorry to be difficult (or dense, whichever it is), but a blank spreadsheet isn't a lot more helpful than an image.

I've entered a formula in cell AO6, but it's a feeble attempt to read your mind on what you want.

What would be helpful is if you'd

1. place some representative data in some of the cells...it's not clear, for example,  if you'll just be entering the number 1 in those cells under the days of the month
2. If there are other numbers, what does each of them represent?
3. What does the "15" represent? Instead of hard-coding, it would be better if we used a named variable. And that goes for the other numbers as well, if there is a "code" or meaning to them.
4. place some desired answers (with explanation) in the columns AO and others associated OR, even better, a formula or two that you've tried, showing what it does produce and contrasting that with what it should and why. Add text boxes or a description of what you're trying to do.

Otherwise, you're kind of asking the folks here to read your mind as to the underlying thought processes. Your preliminary description is OK, but in person, I (and I suspect others) would be still asking a lot of questions. Anticipate those questions..

[#5]

@mathetes 

Dear Mathetes,

to begin with, thank you for your time and efforts.

I have attached a spreadsheet trying to describe what is the situation and what I need to do.

I hope it helps

[#6]

@panagiotisstefanidis 

OK. Thanks for that explanation.

Still have a few questions, however. If I were in your place, I would be approaching this in an altogether different manner.

What you've done here (and I've seen this kind of thing many times before) is to take a paper process, as you basically acknowledge, and transfer it to Excel.  Yet using Excel's clear rows and columns (a visual that looks like paper ledger sheets) solely as an automated ledger sheet is a classic mistake. Instead, it often makes sense to re-think what you're doing:

• what's the goal, or the results desired?
• what is the necessary information?
• what action(s) need to be taken based on that information?
• what is the sequence of events that need to be recorded to fully manage the situation?

In this case, therefore:

• Are you ONLY tracking absences? Is there a default assumption that "no record" means present and accounted for?
• Are the only actions those of notification and potential follow-up? Might there be suspension or other disciplinary action?
• When does the first notification get sent? Easy to see if the absence is one day, the person is back the next day, so a notification gets generated. But if a person is absent continuously, do you wait until they return to send the notification requesting justification?
• How do you process the fact that justification has been submitted and accepted? Does it ever get rejected, and if so, then what?

There may be more questions I haven't thought of, but this is why I said earlier that you should anticipate questions and try to answer them in advance. You know this situation intimately and may be taking things for granted. But if you want to use Excel to its fullest advantages, it helps to really describe the full process.