Title: Excel Formulas Wanted Post by: eddie13 on April 18, 2021, 12:11:27 PM 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.. Title: Re: Excel Formulas Wanted Post by: Insanerman on April 18, 2021, 01:18:27 PM 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.. 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! Title: Re: Excel Formulas Wanted Post by: eddie13 on April 18, 2021, 01:56:21 PM So this is basically what we have...
https://i.ibb.co/DDJZvhR/831-C6-A12-FBCD-4-E7-A-A108-0255-CFC52-F0-F.png (https://ibb.co/JR19GVs) https://i.ibb.co/GvB8cm8/B1-ED8-F43-62-D8-4-C26-B924-A8-DC09-A72-DD8.png (https://ibb.co/0j86h56) 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.. Title: Re: Excel Formulas Wanted Post by: RickDeckard on April 18, 2021, 07:47:46 PM 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. Title: Re: Excel Formulas Wanted Post by: eddie13 on April 18, 2021, 07:57:17 PM 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. I think someone that knows what their doing could do this in like 5 minutes.. Title: Re: Excel Formulas Wanted Post by: RickDeckard on April 18, 2021, 08:30:47 PM 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. I think someone that knows what their doing could do this in like 5 minutes.. 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). Title: Re: Excel Formulas Wanted Post by: Insanerman on April 19, 2021, 01:25:06 AM 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. I think someone that knows what their doing could do this in like 5 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. Title: Re: Excel Formulas Wanted Post by: RickDeckard on April 19, 2021, 11:36:46 PM 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:
https://i.ibb.co/f1R2fCW/table.png 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: Code: =TIMEVALUE(LEFT(C3;2)&":"&RIGHT(C3;2)) 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**: Code: =(F4-E4)*24 If we move to column M, this is the formula I've used in cell M3, M4: Code: =IF(H3>8;8;IF(AND(H3>0;H3<=8);H3;"-")) In cells N3,N4 we have the following code: Code: =IF(AND(M3=8;H3>8);H3-M3;"-") 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) |