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

Copper Contributor

Hi, 

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.

Example
Date: 15th December 2022  
Start Time: 8:30am
Finish Time: 6pm
Time Spent: 9hrs 30mins
Lieu Hours: 2 hours (Overtime hours)
Math: Add
Current Hours: 2 hours

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
Current Hours: 1:30

Any 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

7 Replies

@Andrew_Mommers 

 

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!!

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.

@Andrew_Mommers 

 

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, that

                                                                                    this 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 hours

                                                                                      above?

                                                                                     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=11521...

Hopefully you are able to understand by seeing the table.

Let me know if you have any issue's access it or editing it.

@Andrew_Mommers 

 

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)

 

@Andrew_Mommers 

 

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)

 

@mathetes

I've got it now,
Thanks for trying to help.

This can be closed.