Topic: Excel Formulas Wanted (Read 176 times)

I got around to have some free time and after carefully reading what you wrote I think I've managed to get a solution for your problem. I've made the following table:


This is somewhat what your tables seems to have - a Start Time, End Time, Total Hours, Task 001, Task 002, Task 003, ST and OT - nothing new here.

What you see on the first 4 columns (from range C->F) is basically how I thought regarding getting the difference of hours for the field Total Hours. On column C and D you have the usual military time (0800, 1600, 0800, 1900). To have theses values show up as correct military time (and to prevent errors in the future) you have to select the cells (in this case C3:D4 and then go to Home->Number->Format as Text). The problem here (or at least I saw it as one) is that Excel doesn't automatically detect that those numbers represent the military time so you have to somehow tell to the program that those numbers represent hours. That is what columns E and F are doing , they are taking in the values from the previous cells and "transforming" then in actual time. Here is the formula* that I've used for E3,E4,F3,F4:
Warning: Each cell field in the formulas should be adjusted to the line that we're dealing with. In the following example since E4 is on line 4 you should change the field C3 to C4. Please apply these rationale for the remaining code

Applying that formula to each field will net you the values that you see in columns E and F. This is good, we now have the correct time! So the column H is basically the difference between the Start Time and End Time. In cells H3 and H4 you'll find the following (simple) formula**:
Regarding columns I, J and K I honestly didn't understood - do you fill these manually or do you want them to calculate (in this case equally) for all fields?

If we move to column M, this is the formula I've used in cell M3, M4:
What I'm saying here is that if the value of Total Hours is bigger than 8 then the cell will assume the value of 8. If the value in Total Hours is bigger than 0 and smaller or equal to 8 the value will also be showed (for example any number between 0-8). Warning: This will show up as an error if the difference is negative.

In cells N3,N4 we have the following code:
What I say here is that if M3 is equal to 8 and H3 is bigger than 8, then we have here a difference to make! In this case I just make H3-M3 and the difference will show up in the OT field. If one of these conditions is not met (for example M3 is equal to 5) then no "extra hours" were made and the field of OT will show up as "-".

Let me know if this helped you in any way. Like I said I love Excel and see it as a really powerful tool but sometimes people think that it's too complicated to make certain things - believe me, it is not. I took more time writing you this reply than making the example in Excel, just to give you an idea that for a average user this is a simple thing.

Let me know if I can help you any further!

---

* If you want the process that I've used to build these formulas, but for now I'll just post what I built
** I've multiplied by 24 to have a result in hours and minutes ( say 2.5 represent 2 hours and 30 minutes)

May I know your Telegram? For a convenient mode of communication. I am currently trying to code a cell to save and export your file. Shoot me a PM in tg @Insanerman or just here in this forum. Lastly, may I also get a copy of your file so I can already try it on your format, for both familiarity and application. Thanks! I think i can finish this within this day.

Shoot me a PM then mate! Or then clarify in a clear way what you intend to do here (I didn't quite understood what you want from the screenshots you posted). Could you host a sample file with what you want to do ? Try to remove any kind of personal information before hosting it (I recommend mega.nz for this).

It would help me a lot if you could tell me like this : Look to my cell A2 (for example). I want to take in that value and paste it in cell A10 (for example) and I want it to have X value based on Y criteria. That way I'm able to understand what you aim to do with your file (we can develop this further since it seems that you want to automate even more the sheet, but this would be a nice first step).

I think someone that knows what their doing could do this in like 5 minutes..

Is there a specific time frame for this project @eddie13? As in, you want this to me done ASAP (which I can't compromise) or you are willing to wait a while (say 2-3 weeks)? The reason that I'm asking is that I would be really interesting to know more about your goals because I probably will be able to help you out in some way or other (either by programming or by using formulas).
Let me know if you're interested in this offer or not (by either here or by PM). Excel is a powerful tool that most of the time have simple solution to some problems and I don't want people to charge you a ridiculous fee for a work that may (or may not be) that much work.

So this is basically what we have...





I want to be able to fill in the bottom page and have it automatically fill in the top page..

Bottom page - fill in start and end time in military time format, automatically fill Total Hours, manually divide it between Task 001-Task 003

These numbers then need to automatically port to the top page..
ST (straight time, anything up to 8, if over 8 put the remainder in the OT box)
From Task 001 to HS6301 column, from Task 002 to HS6302 column..

Make sense?

Maybe a couple more things..
It already has a bunch of code in it, half of which doesn’t work correctly.. (as you can see with it splitting the 2 hours between 6301 and 6202 in the top page, no, it needs to do that from the cost hour allocation boxes instead)
Would also like it to take the date and “Customer:” as the file name to save..

I really wanted to try this out, but I'm a little confused with what "have that port to a couple other boxes as 12..." means. If you would let me try this, then I'll send you an output within the next 24 hrs and if I were to get the thought right. There's nothing to lose if I were to try it right? Kindly send me a PM in TG @Insanerman . Thanks!

Anyone good with making excel spreadsheets more automated and want to make some satoshis, let me know..

I basically want to enter 0800 in the time start box and 1600 in the time end box (military time) and have that port to a couple other boxes as 12 and some others as 8 and 4 (if>8 put 8 and remainder 4 into OT box) for straight time and overtime..
Dates need to populate a few boxes too..

Shouldn’t be too complicated..

If this works out then we will probably also like to port some boxes from this daily worksheet into a separate job cost tracking worksheet..
Maybe more..