How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4462
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

Hi Jess, I would like to ask you a question where on this

Resolved Question:

Hi Jess, I would like to ask you a question where on this excel sheet, i have the start date of our clients and every 6 months we need to make a visit, Is there a Formula where we can put it one of the columns as once I put the start date, in the next column it automatically puts in the number of visits needed, for exame 01/01/13, therefore the visits will be 07/01/13, and the next visit is 01/01/14,  so the number of visits needed is 1 and the date required is 07/01/13 and the next one scheduled is 01/01/14, here is the document


 


http://www.wikisend.com/download/444450/SUPv.xlsx

Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Lindie-mod replied 1 year ago.

Hello,

I'm Lindie, and I’m a moderator for this topic. I sent your requested professional a message to follow up with you here, when they are back online.

If I can help further, please let me know. Thank you for your continued patience.

Best,

Lindie

Expert:  Jess M. replied 1 year ago.
Hi,

Thank you for your question. I got the Excel file and I want to know which column do you want this done? Is this in the SUP Visits column?

If it is so, let us validate the rule that you want in place. For instance, in your file, the start is 7/10/2014 and the DUE date is 1/10/2014. Isn't it 1/10/2015? The 6th month visit is 1/10/2015 and the SUP visits value must be 1, not 0.

Or are you concern of the DUE column? Do you want to automatically calculate the 6-th month dates there?

Please confirm.

Jess
Customer: replied 1 year ago.

Thank you for your reps Jess, yes for your example, Start date is 7/10/2014, and the present date is 01/30/15, then I would like a column, that will show the Number of Visits which is 1 , and the other column shows me the date of 01/10/2015 as the visit or visits date, and the other column of the next SUP visit which is 7/10/2015,

Expert:  Jess M. replied 1 year ago.
How many 6-th month visit dates do you want?

For example Start date is 7/10/2014.
The 1st visit date is 01/10/2015
The 2nd visit date is 7/10/2015.

The 1st and 2nd visit dates will be entered automatically into their individual columns?

Also, up to HOW MANY 6th month visit dates do you want computed?

Jess
Customer: replied 1 year ago.

Upto 6 say for example or can we go to any number, for example the client was on 07/10/2011 then today we would have needed to complete visits on the 01/10/2012, 07/10/2012, 01/10/2013, 07/10/2013 so 4 SUP visits would have needed to be completed, and the fifth visit would be on the 01/10/2014, May be 6 would be the maximum,

Expert:  Jess M. replied 1 year ago.
Can I modify your Excel file to add this 6 columns? I will try to work on this now and will give you the completed file in a while.

Jess
Customer: replied 1 year ago.

Yes that's perfectly fine

Expert:  Jess M. replied 1 year ago.
Hi,

Thank you for patiently waiting. I have completed the file for you and you can download it in this link:

http://wikisend.com/download/116594/SUPv_rev1.xlsx

The formula used was
=IF(C4="","",DATE(YEAR(C4),MONTH(C4)+6,(DAY(C4))))

The IF controls the blanks so that no date will be added when the C cell is empty. To add the 6th-month visits, we need to use the DATE function adding the 6 to the month for the first visit, 12 to the second, 18 to the third, 24 to the fourth, 30 to the fifth and 36 to the sixth visit.

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4462
Experience: Computer Software Specialist for more than 10 years
Jess M. and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Is there a way where the SUP visits column gets automatically gets filled

Customer: replied 1 year ago.

Hello Jess, Thank you very much for your answer, Is there a way where the SUP visits column gets automatically filled

Expert:  Jess M. replied 1 year ago.
What are the conditions in counting or making a count for SUP visits? From those conditions, we can derive a formula.

Jess
Customer: replied 1 year ago.

The conditions are


If it more the 6 months and less than one year, from the start date to present date of today it is = 1, and if it is more than 1 year and less than 18 months it is equal to 2,


 

Expert:  Jess M. replied 1 year ago.
Thank you for that information.

So are you saying that TAKE these visits (whatever these visits mean) as DONE when VISIT date has elapsed?

For instance, if the 4th visit date is 10/5/2013, SUP Visit = 4 since today is past 10/5/2013?

Is this correct?

Jess
Customer: replied 1 year ago.

Yes Jess, when the date has passed that is exactly what i was saying, 10/5/2013 then SUP Visit =4

Expert:  Jess M. replied 1 year ago.
Thank you for that confirmation. This is kinda tricky. Please give me a moment to create a formula for your SUP visits column.

I will get back to you with the final file.

Regards,
Jess
Expert:  Jess M. replied 1 year ago.
Hi,

The requirement was in fact tricky. After several tries, yes, we can automatically fill the SUP visits column based on the dates.

Here is the formula used:
=IF(AND(E4="",F4="",G4="",H4="",I4="",J4=""),"",COUNTIF(E4:J4,"<="&TODAY()))

The IF function there actually tests the contents of the 6 visit dates. If ALL 6 visit dates cells are blank, the SUP visit cell must be blank as well. If NOT all of the cells are blank, of if all cells are filled with Visit Dates, then it will be counted IF the dates are less than or equal to today's date.

You can download the file with this modifications in the link below:
http://wikisend.com/download/653878/SUPv_rev2.xlsx

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4462
Experience: Computer Software Specialist for more than 10 years
Jess M. and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Thank you very much Jess for the answer, and the time, I will take note of the formula that you had entered on a side one can you tell me what is the "" on the formula line Thank you once again,


 


Thanking YOu


 


Mark

Expert:  Jess M. replied 1 year ago.
Hi Mark,

Thank you for writing back and I am sorry for this late reply. I am living in a different time zone and by the time of this writing it is 12:15PM in my country. My last answer to you was 4AM yesterday Cool.

I gave you 2 formulas. One for the calculation of the 6th-month visit date and the other is the counting of the SUP visits.

Here are the explanation:

Calculating 6th-month visit dates
=IF(C4="","",DATE(YEAR(C4),MONTH(C4)+6,(DAY(C4))))

In that formula, the actual computation of the 6th-month date from the Start Date is this part:

=DATE(YEAR(C4),MONTH(C4)+6,(DAY(C4)))

That formula will ADD 6 to the month found in column C. However, to clean up your worksheet, I added a TEST formula to see if the Start Date is BLANK. Here is that TEST formula in bold:

=IF(C4="","",DATE(YEAR(C4),MONTH(C4)+6,(DAY(C4))))

The formula means:

IF C4 (the cell containing Start Date) is BLANK, ("" means blank in Excel), the cell (the cell which is supposed to show the 6th-month date) will show BLANK (or "").

IF C4 is NOT BLANK, then the cell will display the result of the formula DATE(YEAR(C4),MONTH(C4)+6,(DAY(C4))).

The IF function in Excel has this format:

IF(logical test that results to True or False, Value if True, Value if False).

Counting the SUP Visits
=IF(AND(E4="",F4="",G4="",H4="",I4="",J4=""),"",COUNTIF(E4:J4,"<="&TODAY()))

In that formula above, again, I added a TEST formula to cleanup the results to be displayed. The actual formula that does the counting of SUP Visits is this:

COUNTIF(E4:J4,"<="&TODAY())

That formula will COUNT the cells in the range E4 through J4, which correspond to the 6 visits. However, the cells are only COUNTed when the date in the cell is less than or equal to today's date. Since the formula is using the volatile TODAY() function, each time you close your file, it will always prompt you to Save or Not even though you did not change any thing in the worksheet. The reason for this is because the TODAY function takes a dynamic date so Excel takes this new and continually new date as a change in the file causing it to prompt to save your changes.

The TEST or cleanup part that I added was in bold:
=IF(AND(E4="",F4="",G4="",H4="",I4="",J4=""),"",COUNTIF(E4:J4,"<="&TODAY()))

It may appear tricky because of he AND function. Remember that IF tests a logical expression that results to True or False. Then the next argument is Value IF True, and the last argument is Value IF False.

Here is a sample:

=IF(A1 > 70, "Pass", "Fail")

In that formula, a grade is tested if it passed or failed. If A1 has 64, since it is NOT > 70, it is false so Fail will be shown.

In the TEST formula that I built for you, the logical expression tested is the AND function there since AND function will result to True or False. The AND means ALL tests must be True to result to True. Likewise, all tests must be false to return False.

Here is the AND part:
AND(E4="",F4="",G4="",H4="",I4="",J4="")

Remember that "" means blank. If E4, G4, H4, I4, and J4 are all blank cells, the AND expression results to True. If any of the cells E4, G4, H4, I4, and J4 has a content, the AND will give False.

So IF all cells are blank, the SUP Visit value must be blank ( or "" in Excel expression). Otherwise, show the results of the formula COUNTIF(E4:J4,"<="&TODAY()).

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4462
Experience: Computer Software Specialist for more than 10 years
Jess M. and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Thank you Very much Jess, for your time and answer and for the detailed explainations on the formula, thank you once again. I understand about the time zones and I am always patient.

 

Sincerely

Mark

Expert:  Jess M. replied 1 year ago.
Mark,

You're always welcome. I am glad to be of help. If you need assistance in the future, you can request me any time.

Best regards,
Jess

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
Jess M.
Jess M.
329 Satisfied Customers
Computer Software Specialist for more than 10 years