Help to create a sheet where if text is add/subtract to do that math

Question

Hi,&nbsp;I am trying to create a sheet where you put in the date, start time and finish time, time spent, lieu hours, math (Add/Subtract) and current hours.ExampleDate: 15th December 2022&nbsp;&nbsp;Start Time: 8:30amFinish Time: 6pmTime Spent: 9hrs 30minsLieu Hours: 2 hours (Overtime hours)Math: AddCurrent Hours: 2 hoursIf I have a longer lunch, I add it to Time In Lieu and put it in the sheet.Time Spent: 30minsLieu Hours 0:30Math: SubtractCurrent Hours: 1:30Any help would be great, thanks.I just need to do the Current hours, I've gotten the Lieu Hours just need help doing the text do math.Thanks,Andrew

mathetes · Answer

Andrew_Mommers&nbsp;&nbsp;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."&nbsp;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.&nbsp;I'm sure it's all clear to YOU, and therefore you think it's clear. But, sorry to say it, it ain't!!

andrew_mommers · Answer

Hi Mathetes,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.

mathetes · Answer

Andrew_Mommers&nbsp;&nbsp;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 writtenExampleDate: 15th December 2022&nbsp;&nbsp;Start Time: 8:30amFinish Time: 6pmTime Spent: 9hrs 30minsLieu Hours: 2 hours (Overtime hours) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;I believe, based on your most recent comment, that&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; this is because you went two hours beyond 4 p.m.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Is that correct?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; There's no indication of that, however,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; in your first example. It just appears&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and is labeled as "Leiu Hours"Math: Add &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Add what to what??Current Hours: 2&nbsp;hours &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Where does this number come from?&nbsp;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: 30minsLieu Hours 0:30Math: Subtract &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Subtract what from what?&nbsp;Current Hours: 1:30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Is this the result of subtracting 30 from the 2 hours&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; above?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;It probably is, but you were assuming&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;that is obvious when it isn't.&nbsp;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.

andrew_mommers · Answer

Hi Mattmetes,Oh okay, I get it now.Excel Sheet linkhttps://docs.google.com/spreadsheets/d/1CILovsvM_LP1YYHZeKxRYJALzNIqn4C3/edit?usp=sharing&amp;ouid=115216219569436501114&amp;rtpof=true&amp;sd=trueHopefully you are able to understand by seeing the table.Let me know if you have any issue's access it or editing it.

mathetes · Answer

Andrew_Mommers&nbsp;&nbsp;NO issues with accessing the Google Doc. Thank you.&nbsp;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.&nbsp;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" andwhat columns would be involved in subtraction, when the word is "Subtract"&nbsp;It seems as if it's the IF or IFS function that you are wanting help with....so spell out in wordsIF (E3="Add",.... then (do this, in words)IF (E4-"Subtract"...then (do this, in words)&nbsp;

Forum Discussion

Help to create a sheet where if text is add/subtract to do that math

7 Replies

Resources