PDA

View Full Version : [SOLVED] Help needed with some excel formula - hopefully someone can assist.



spittingfire
01-29-2015, 07:06 AM
Hi All,

I am in need of some help with some excel formula or vba (whichever makes more sense or simpler) for my workbook.

Attached is a sample of data.

What I need to do are the following:

1. Ultimately I will need to have four additional columns that I will use to upload the results to another system. More columns can be added for additional calculations if necessary (I don't have any issues with that). The four additional columns are currently L, M, N & O and can be shifted if needed.

2. The Time column "L" will need to have the start time of the FLTR while column "N" the start time of the PAY-. So basically if the Start time in "G" is blank then use the start from "D" and if the Start time from "G" is later than "D" then use the time from "G".

3. The FLTR and PAY- will come from a combination of columns H, I & J and that is where it becomes hazy for me.

3b. Duration - if the duration "H" is blank (FULL DAY) then use "F" minus "D" to get the duration time.

3c. If the balance "J" is less than or equal to 0 then duration time will be PAY-.
3d. If there is a balance in "J" but the reason in "I" is PAY- then we go with PAY-.
3e. If there is a balance in "J" and the reason in "I" is FLTR then we go with the FLTR if the balance is enough.
3f. Continuing from 3e (above) - if the balance is not enough to cover FLTR then we first use the FLTR and the balance will be PAY-. In this case we will have two times and two durations. For example Balance = 4:00 but reported a FULL DAY FLTR for 8:00. FLTR will be 4:00 (08:00 - 12:00) and PAY- will be 4:00 (12:00 - 16:00).
3g. Two last important exceptions. If the balance "J" = 7:30 and the reason "I" is FLTR and duration "H" is 8:00 then this ok and will go under FLTR and the other exception is If the balance "J" = 8:00 and the reason "I" is FLTR and duration "H" is 8:30 then this ok and will also go under FLTR. For the two exceptions all conditions much be met for it to be FLTR. If one or more condition(s) is not met then it will be PAY-.

Please note that columns "M" FLTR & "O" PAY- will be the duration time.

Hopefully I am clear enough with what I am looking for and hopefully you can assist me with this.

Thanks in advance for all your help.