Forum Discussion
Help to create a sheet where if text is add/subtract to do that math
You've had 60 views as of this writing, but no replies. I suspect that lack of response is because it's really not at all clear what you are asking to do. Something along the lines of "If I enter the word 'subtract' I want Excel to do that subtraction." And./Or "If I enter the word 'add' then it should do addition."
But your examples don't make sense: what exactly are we adding to produce what number? What are we subtracting to produce what number? And so forth.
I'm sure it's all clear to YOU, and therefore you think it's clear. But, sorry to say it, it ain't!!
Thank you for letting me know that it is not clear.
Apologies for the bad English.
I am trying to make a Time in Lieu Bank Sheet in Excel.
I want to make it, so it automates my current hours from the start and finish time.
My contracted finish time is at 4pm, If I go over time, I need to put it in my Time in Lieu Bank.
I want it so If I put in the start of lieu time and finish time of that and select Add than it will do the math and give me my current hours. If I use some of my Lieu time, I select subtract and it will update my current hours.
Please let me know if that does not make sense.
What is not clear is what numbers are to be added, subtracted in order to produce what results. You need to give some clear examples. Multiple examples.
Here's what you've written
Example
Date: 15th December 2022
Start Time: 8:30am
Finish Time: 6pm
Time Spent: 9hrs 30mins
Lieu Hours: 2 hours (Overtime hours) I believe, based on your most recent comment, thatthis is because you went two hours beyond 4 p.m.
Is that correct?There's no indication of that, however,
in your first example. It just appears
and is labeled as "Leiu Hours"
Math: Add Add what to what??
Current Hours: 2 hours Where does this number come from?You then went on to offer this:
If I have a longer lunch, I add it to Time In Lieu and put it in the sheet.
Time Spent: 30mins
Lieu Hours 0:30
Math: Subtract Subtract what from what?
Current Hours: 1:30 Is this the result of subtracting 30 from the 2 hoursabove?
It probably is, but you were assuming
that is obvious when it isn't.
Since you have some of it resolved already, would you please post a copy of what you have done already. It would make it easier to fit what is missing into what you already have. Put a copy on GoogleDrive and past a link here that grants access.
- Hi Mattmetes,
Oh okay, I get it now.
Excel Sheet link
https://docs.google.com/spreadsheets/d/1CILovsvM_LP1YYHZeKxRYJALzNIqn4C3/edit?usp=sharing&ouid=115216219569436501114&rtpof=true&sd=true
Hopefully you are able to understand by seeing the table.
Let me know if you have any issue's access it or editing it.NO issues with accessing the Google Doc. Thank you.
But I don't understand what it is you want done. And, quite frankly, don't understand why you don't just write the formula that DOES the adding or subtracting. You clearly know how to write formulas.
But if you wish help, please describe in words, now, based on the data in the various columns of that Google Spreadsheet,
what columns you'd want to have added when the word in Column E is "Add" and
what columns would be involved in subtraction, when the word is "Subtract"
It seems as if it's the IF or IFS function that you are wanting help with....so spell out in words
IF (E3="Add",.... then (do this, in words)
IF (E4-"Subtract"...then (do this, in words)