Multiple Problems: complex IF statement, time calculations, and column marking

[Colby Wolford]

I am a graduating from university this December as a high school English teacher. However, in the meantime, I am freelancing some of my talents (transcription, proofreading, editing, and such) on some freelance websites. Because of this, I'm trying to create a project spreadsheet. I've got most of it finished, but I'm having problems with a few features that I would like it to have. I have uploaded a copy of the file so you can follow what I'm (hopefully) explaining.


File: https://docs.google.com/file/d/0B4jd0Jt2LQq-VFh6cHc2Yml3Y0U/edit?usp=sharing


Issue 1: Time Calculation.

Under the heading of Dates, I have a "Start," "Complete," "Time," and "End." Start/Completion dates track how much time I spend on the project and End tracks when the client ends the contract. I want to set up a simple function to subtract the start date, hours, and minutes from the completion date, hours, and minutes to give me the time spent on the project. (From their I can calculate my average time based upon project type.) Right now, all I have is a formula that subtracting the Start date from the Completion date; but I want to get more specific. However, I don't know how to format the cell or enter the date and time to do this.


Issue 2 & 3: Complex IF statement and Column marking.

Using the "End" column and my "Paid" column (just cells with an "x" showing that payment is pending or has been received), I want to input a formula that will calculate whether the client is overdue in payment or ending the contract (+7 days from completion [if End is blank] or +7 days from End if payment hasn't been received, I want an "x" to appear in the "PD" column (PD = past due) and I want the same formula for the TA (TA = take action) column if the calculations are greater than or equal to 14 days from the completion and end dates). I hope that wasn't too confusing. Also, once the date is +14 days, I want the "x" in the PD column to vanish and appear in the TA column so that only one column is marked.


So with these explanations and a copy of my file, I would really appreciate it if someone could set me straight.


Thank you!


Colby

 

[NARAYANK991]

Hi Colby ,


First , any date and time combination can be entered with a space between them , as in :


06/22/2013 07:45:25 AM


where I have entered the date in mm/dd/yyyy format because that is the default format in my PC.


Second , how do you want the data in the Time column to be displayed ? Do you want that it should display in Days , Hours , Minutes , Seconds or should it display in decimals ?


For example , suppose we have the following two items of data :


06/22/2013 07:45:25 AM


06/26/2013 07:35:22 PM


The difference between the above two date and time combinations is displayed as :


4.493020833 if the cell format is General


04 11:49:57 if the cell format is dd hh:mm:ss


Narayan

 

[SirJB7]

Hi, Colby Wolford!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Little details but important if not detected:

a) Columns D, E & G should be all of the same width and equally formatted: D&E have US date format notation and G display all "#" since it has regional settings notation and in non-US Excel versions it will behave differently to the others.

b) Tables or ranges with borders should have all borders: check O12:O22 and A22:O22.


Now the main issues:


1) Excel handles dates as integer numbers starting with 0 for 31/12/1899 (in fact with 1 for 01/01/1900 but it accepts zero as well) and times as fractional numbers from 0 to 0,999988426 (from 00:00:00 to 23:59:59).

Excel formats allow you to format a cell as dd/mm/yyyy, another as hh:mm:ss, and another as "dd/mm/yyyy hh:mm:ss" (unquoted), so the answer to your question will depend on how would you format actual columns D:E & G, how would you calculate F.

If we assume that you'll use the full format (last) for column F you'd use a simple formula like this:

=SI(D15*E15=0;"";E15-D15) -----> in english: =IF(D15*E15=0,"",E15-D15)

... if the time has no interval (i.e., full 24h and not working time from Mon-Fri 8-17); if it has these kind of calculations, it'd be very more complex.

If you choose to split D/E/G in two each one, for date and time, you still may use the same formula construction but replacing D15 by D15+E15, E15 by F15+G15 and G15 by I15+J15 (or J15+K15 regarding what would you do with F.


2) Try this:


PD: =SI(O(Y(NO(ESBLANCO(E14)); HOY()>=E14+7; HOY()<E14+14; ESBLANCO(G14); ESBLANCO(H14)); Y(NO(ESBLANCO(E14)); HOY()>=G14+7; HOY()<G14+14; ESBLANCO(H14))); "x"; "") -----> in english: =IF(OR(AND(NOT(ISBLANK(E14)), TODAY()>=E14+7, TODAY()<E14+14, ISBLANK(G14), ISBLANK(H14)), AND(NOT(ISBLANK(E14)), TODAY()>=G14+7, TODAY()<G14+14, ISBLANK(H14))), "x", "")


TA: =SI(O(Y(NO(ESBLANCO(E14)); HOY()>=E14+14; ESBLANCO(G14); ESBLANCO(H14)); Y(NO(ESBLANCO(E14)); HOY()<G14+14; ESBLANCO(H14))); "x"; "") -----> in english: =IF(OR(AND(NOT(ISBLANK(E14)), TODAY()>=E14+14, ISBLANK(G14), ISBLANK(H14)), AND(NOT(ISBLANK(E14)), TODAY()<G14+14, ISBLANK(H14))), "x", "")


Just advise if any issue.


Regards!

 

[Colby Wolford]

Thank you both so much for your quick replies!


In formatting and entering date and time (m/dd/yyyy hh:mm) into my Started and Completed columns and then changing the formula in Time to =IF(D15*E15=0,"",E15-D15), the result is only a difference in hh:mm, but it does not account for the difference in m/dd/yyyy. So if I enter 6/15/2013 7:00 AM into Started and 6/18/2013 10:00 PM into Completed, my Time shows as 15:00, even though the result should be 87:00. How do I correct this?


Also, SirJB7, the formulas for PD and TA work wonderfully, but I've noticed that if the time is +14 days, the "x" still remains in PD. Is there a way to make the "x" in PD disappear when the "x" is shown in TA?


Thanks again for helping me!

 

[NARAYANK991]

Hi Colby ,


Use the format [hh]:mm:ss to show hours greater than 23.


Narayan

 

[Colby Wolford]

Narayan -


Perfect! Thank you!


I love having problems because I learn so much through experimenting and/or getting answers.


You folks rock!

 

[SirJB7]

Hi, Colby Wolford!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Multiple%20Problems_%20complex%20IF%20statement%2C%20time%20calculations%2C%20and%20column%20marking%20-%20Ledger-1%20%28for%20Colby%20Wolford%20at%20chandoo.org%29.xlsx


It's your uploaded file with borders and my formulas for PD & TA. Please check them and if they don't work, upload again the same file with the correctly working and non-working example entries. Thank you.


Regards!