A little help combining formulas...

[PLance1]

Could anyone help me with this?

I’ve got a spreadsheet and I have three columns that are giving me a little trouble.

Column K is my start time. Column L is my end time. Column M is the number of hours that are created.

I created a formula that adds up the time, note that I’m only concerned about the number of hours up to 8, so even if there are more than 8 hours, the max number in this column would be 8 since this column is only used to document straight time:

=IF(M13>8,8,(8-M13)) this formula works

If someone doesn’t enter a start time, I want Column M to read “Enter Start Time” so I made this formula:

=IF(K18="",”Enter Start Time”,0) and this works fine.

But once a Start Time is entered, I want Column M to change to read “Enter End Time”

And once an End Time is entered I want the formula to work to add up the hours.

If someone doesn’t enter either a start time or an end time, I want Column M to read “Enter Time”

So I can do bits and pieces of what I want but not everything I want. Could someone please help? I've watched a few youtube videos but did not get very far.

Thanks in advance.

[Mazzaropi]

PLance1, Good evening.

Try to use:

=IF(AND(K13="",L13=""),"Enter Time",IF(AND(K13="",L13<>""),"Enter Start Time", IF(AND(K13<>"",L13=""),"Enter End Time",IF(M13>TIMEVALUE("08:00"),TIMEVALUE("08:00"),TIMEVALUE("08:00")-M13))))

I tested it and it worked.

I hope it helps.

[PLance1]

I couldn't get it to work... and I went back and reread my post. I was mistaken, I don't need to worry about whether or not the value in M exceeds 8, I actually have another column for that... so all I want it to to do is add up the total number of hours if both a start time and and an end time is entered. But I want to keep the who idea of having text that says "enter start time", "enter end time", "Enter time".... but I will try working with your formula. Thank you very much.

[dflak]

For future reference, people don't normally "bite" on posts that don't have a sample spreadsheet attached. That's because there is work involved with setting it up and because it is possible to set it up incorrectly. However, this was a simple enough description so set up was easy.

I noticed a couple of things:

Your formula =IF(M13>8,8,(8-M13)) shouldn't work if you are entering times as times. The reason for this is that Excel measures dates as days + time. The whole part is the day part and the fractional part is the time part. So 6 AM is a quarter of the way through the day so internally Excel records it as 0.25. To convert a time of day to hours, you have to multiply it by 24. This will give you hours and fractions of hours. If you want, you could further refine the fractions of hours into minutes and then seconds.

However, you want to do math using the start times and end times so multiplying the time by 24 and working with hours and fractional hours is just fine.

The logic of your IF statement is correct.

I used a different formula (there are usually several different ways of doing things in Excel) =IFERROR(MIN(24*(L2-K2),8),"").

Let's take this apart piece by piece from the "inside" (the best way to interpret Excel Formulas)

L2-K2 is simply the differences in the time. This is the result of two fractional parts of a day being subtracted, so we have to multiply it by 24 to get the number of hours.

Then I used the MIN function to compare the result with 8. MIN will give 8 or the computed value, whichever is lower.

Then I wrapped the whole thing in an IFERROR statement. Originally I had column N formatted as time. I reformatted it to a regular number. You can't have a negative time in Excel. So if that happened, I wanted the cell to show nothing. You want to see hours and fractions of hours not hours and minutes.

Now for the verbiage you want. This will take nested if statements.
=IF(AND(K2="",L2=""),"Enter a Time",IF(AND(K2<>"",L2=""),"Enter an End Time",IF(AND(K2="",L2<>""),"Enter a Start Time","")))

I started with the worst case scenario first: no data in either cell. If that's true then the IF statement stops there. But if it isn't true, then what? We need another if statement for the next condition: I have a start time but no end time, if that evaluates to false, we move onto the next check: we have an end time but no start time. The only thing left is when both cells are filled in and we'll assign a blank to that.

The other thing you might want to look into is Data Validation - apply a validation on the cells to check that they are times between your shift start and shift end or 12 AM and 11:59:59 PM.

You may also want to add some additional checks to make sure that the start time is not later than the end time.

[PLance1]

Thank you, I will review your comments, I was not aware that I could attach a spreadsheet. I've tried to attach it here.. The columns in question are in yellow. I am trying several different formula's in various rows. Again, I will review your post right now, thank you for being so thoughtful.

[dflak]

I did not read the requirement properly and gave an incomplete solution. You wanted everything in one cell. The logic described above still works, it's just that I combined the formulas.

=IF(AND(K2="",L2=""),"Enter a Time",IF(AND(K2<>"",L2=""),"Enter an End Time",IF(AND(K2="",L2<>""),"Enter a Start Time",MIN(24*(L2-K2),8))))

All this looks very complicated, but when you take it piece by piece it becomes easier.

[PLance1]

Holy cow it works!!! See attached. I put your formula where the dark blue is. And I don't really have to worry about negative values in excel and problems with going over a day because I'm only doing the calculation for one day at a time.. So Yes, this is exactly what I want.... but how in the world would i ever learn how to do this on my own??? Where do I begin...just understanding the syntax is hard for me.