MoonPoint Support Logo

 


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
January
Sun Mon Tue Wed Thu Fri Sat
 
17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      
2018
Months
JanFeb Mar
Apr May Jun
Jul Aug Sep
Oct Nov Dec


Sat, Jan 07, 2017 11:12 pm

Day of the week for dates in Excel and Calc

If you wish to display the day of the week, i.e, Monday, Tuesday, Wednesday, etc., that corresponds to a particular date in Microsoft Excel you can simply format the display of the date using a custom format of ddd. E.g., in Microsoft Excel 2013, if I have dates in column A displayed in mm/dd/yyyy format, but I also want the day of the week corresponding to those days to appear in column B as shown below.

Excel 2013 dates with days of the week

[ More Info ]

[/os/windows/office/excel] permanent link

Fri, Mar 04, 2016 9:37 pm

Workday function

Sometimes you may need to determine the number of work days from one date to another in a Microsoft Excel, Google Sheets, or Apache OpenOffice Calc spreadsheet. E.g., you may need to exclude Saturdays, Sundays, and holidays from a calculation. All three spreadsheet programs provide a WORKDAY function that you can use to calculate dates based on business work days rather than just the total number of days from a start date. For Microsoft Excel on both Microsoft Windows and OS X and Google Sheets, the syntax for the WORKDAY function is as follows:

WORKDAY(start_date, num_days, [holidays])

For OpenOffice Calc, you need to separate the parameters with a semicolon rather than a colon. I.e.:

WORKDAY(Start_date; num_days; [holidays])

If you separate the parameters with a comma in Calc, it will display Err:508.

"Holidays" is enclosed in brackets to indicate it is an optional parameter, you would not actually include the brackets if you specified holidays.

The arguments to the function have the following meaning:

E.g., I need to determine the date when work requests that should be completed within 5 business days after approval should be completed. I can display the required implementation date for each request in a worksheet by using the formula =WORKDAY(cell,5) where cell holds the approval date. E.g., if the approval date is in cell A2, I can set the formula for the required implemenation date to be WORKDAY(A2,5) for Excel or Google Sheets or WORKDAY(A2;5) for Apache OpenOffice Calc.

If there was no date in cell A2, Excel will display January 6, 1900 in the cell holding the implemenation date, e.g. 1/6/00, if the date format is D/M/YY (day/month/2-digit year), but OpenOffice Calc and Google Sheets will display January 5, 1900. Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations and, by default, uses January 1, 1900 as serial number 1. If you have a date field containing January 1, 2008 that is equivalent to 39,448, which you would see if you put that day in a cell formatted to hold a date then changed the format to be a number. January 1, 2016 is 42,370. January 1, 2008 is 39,448 days after January 1, 1900 and January 1, 2016 is 42,370 days after that date.

Why do Google Sheets and Apache OpenOffice Calc show January 5, 1900 as the calculated date when there is no date in the start_date field while Microsot Excel displays January 6, 1900? Long before Microsoft became a dominant player in the spreadsheet market, Lotus 1-2-3 from Lotus Development Corporation was a dominant spreadsheet in the personal computer market. There was a bug in Lotus 1-2-3 due to the developers assuming that the year 1900 was a leap year; it was not. When Microsoft released Excel they had to compete with Lotus 1-2-3, the then dominant spreadsheet for personal computers, so Microsoft needed to ensure that spreadsheets created in Lotus 1-2-3 returned the same results in Excel, so for compatibility retained the incorrect assumption regarding the year 1900 - see the Microsoft article Excel incorrectly assumes that the year 1900 is a leap year and Excel Date Conversion (Days from 1900) regarding making an allowance for that incorrect assumption. Google Sheets and Apache OpenOffice Calc did not retain that incorrect assumption regarding the year 1900 and so you will see January 5 used in certain calculations in those spreadsheets where January 6, 1900 is used by Exel, which is why sometims you may get differing results for date calculations between those spreadsheet programs.

If an argument to the workday function is not a valid date, e.g., 2/30/16 for February 30, 2016, which is not a valid date, you will see #VALUE! displayed by the function in all three spreadsheets. In all three spreadsheets, if days is not an integer, the number is truncated to an integer value. E.g., if you used the formula =WORKDAY(A2,5.7) you would get the same result as entering WORKDAY(A2,5).

[/os/windows/office/excel] permanent link

Tue, Jan 05, 2016 9:56 pm

Calculating an end date given the number of weeks from a start date

In spreadsheet programs such as Microsoft Excel, OpenOffice Calc, and Google Sheets, you can determine an end date given a start date and the number of weeks that some entry, such as a class, will last by using the formula =startdate + numweeks * 7. E.g., if the start date is January 11, 2016, which is stored in cell C2 as 1/11/16 (assuming you are using the U.S. MM/DD/YY date format) and the end date is to be stored in D2 and the duration is 10 weeks, you could put the formula =C2 + 10 * 7 in the D2 cell. The result should be 3/21/16. If you wanted to know the date 10 weeks from today and if today is January 5, 2016, you could use =TODAY() + 10 * 7 . In mathematics and normally in computer operations the order of precedence results in multiplication and division having precedence over addition and substraction, so ten will be multiplied by seven and that result added to the current date to determine the last day of the event.

[/os/windows/office/excel] permanent link

Tue, Aug 18, 2015 9:38 pm

Counting number of cells with date before or equal to today

If you have a range of cells in an Excel worksheet that contain a date and you wish to obtain a count of the number of those cells that have a date that is prior to or equal to today, you could use the formula below, presuming the relevant cells containing the date are in the range E2:E810:

COUNTIF(E2:E810,"<="&TODAY())

The ampersand concatenates today's date after the <=. If you wanted a count for only those dates prior to today, but not including today, you could replace the <= with <.

If you need to count cells based on multiple criteria, you can use COUNTIFS. E.g., if for the above range of cells I want to count the number of cells that not only have a date prior to or equal to today, but only count the cells if there is some value in another cell in the row, i.e., if the date in column E is prior or equal to today and there is some value in column P in that row, i.e., column P is not blank, I can use the formula below:

=COUNTIFS(E2:E810,"<="&TODAY(), P2:P810, "<>"&"")

E.g., I need to track when an expiration date stored in column E for an item has passed, but I only want to count the entries when I've contacted the user associated with the item notifying him that he needs to renew the item. In column P in the worksheet, I store the date I contacted the user. If I haven't yet contacted the user, the cell is empty.

I also want to now how many expiration notices I sent today, so use the formula below for that number:

=COUNTIFS(E2:E810,"<="&TODAY(), P2:P810, "="&TODAY())

The formula checks the values in column E from E2 to E810 to determine if the date in those cells is less than or equal to today and, if so, whether today's date is in the corresponding cell in column P. If both criteria are met, then the count is incremented by 1.

References:

  1. Countif greater than or equal to a date
    Date: February 13, 2015
    MoonPoint Support
  2. Excel countifs function
    Date: March 14, 2014
    MoonPoint Support
  3. COUNTIF in Excel - count if not blank, greater than, duplicate or unique
    By: Svetlana Cheusheva
    AbleBits

[/os/windows/office/excel] permanent link

Wed, Jun 03, 2015 10:30 pm

Importing Amazon reports into Excel 2013

To import an Amazon affiliate report into Excel 2013, download the report as a tab separated values (TSV) report, then take the following steps in Microsoft Excel 2013 to import the data, which can also be used for any text file containing tab delimited data:
  1. Click on File.
  2. Click on Open.
  3. Under Open select the location where the downloaded file is located and select the report.txt file you downloaded.
  4. A "Text Import Wizard Step 1 of 3" window will open. You can check the check box next to "My data has headers". You can skip the first title row, if you wish, by changing the "Start import at row" value from the default value of 1. Click on Next when you've made any changes needed.

    Text Import Wizard Step 1 of 3

  5. At step 2, leave "Tab" selected as the delimiter and click on Next.

    Text Import Wizard Step 2 of 3

  6. At step 3, you can leave "General" selected for the "Column data format" and click on Finish.

    Text Import Wizard Step 3 of 3

Once the data is imported, you can click on the vertical lines between the column headers to adjust the width of individual columns to accomodate column titles and data. Or to have the text wrap within the current column width, you can click on a column designator, such as "A", "B", etc., or click on the "1" to the left of the first row to choose all columns on that row, and then right-click and choose Format Cells, Alignment, and then select Wrap Text under Text Control, and then click on OK.

To save the file in an Excel spreadsheet format, select File and Save As, select a location and name for the file, and then change the "Save as type" value from "Text (Tab delimited) (*.txt)" to "Excel Workbook (*.xlsx)" or other file type.

[/os/windows/office/excel] permanent link

Wed, Apr 29, 2015 11:17 pm

Importing text data from a webpage into Excel 2013

I needed to import a whitelist consisting of a list of fully qualified domain names from a web page into a Microsoft Excel spreadsheet. The web page was just a text file with one entry per line, which made it easy to import data directly from the page in Excel 2013. The steps to import such data from a web page are as follows:
  1. Click on Data from the menu at the top of the Excel window.
  2. Select Get External Data and From Web.

    Get External Data From Web

  3. Type the URL for the webpage from which you wish to obtain the data into the Address bar of the New Web Query window which will have opened.

    New Web Query

  4. The contents of the webpage will be displayed and you can click on the Import button.
  5. You will then be asked "Where do you want to put the data?" The default location will be $A$1, i.e., the cell in the first column and first row.

    Import Data

    You can change the location or leave it as is and then click on OK to have the data inserted in the sheet in the Excel workbook

If you need to update the data from the webpage when working in the spreadsheet later, you can select Refresh All all from the Excel toolbar and then select Refresh.

Import Data

The contents of the sheet will then be refreshed with the latest data from the web page.

[/os/windows/office/excel] permanent link

Thu, Feb 26, 2015 5:37 pm

Conditional formatting with "and" and "or"

I have a spreadsheet I maintain with Microsoft® Excel® 2008 for Mac that I use to track work requests. I color code cells in the spreadsheet so that I can easily identify requests requiring attention or that I need to keep a closer eye on. I use Excel's conditional formatting capability to implement the color coding. Excel only provides a maximum of 3 conditions for conditional formatting, but you can employ formulas using the logical "and" and "or" connectives to use multiple criteria for an individual condition to provide additional flexibility with conditional formatting.

E.g., I have an "Expedite" column, column "O", in the spreadsheet where I place a "Y", if the work request requires an expedited implementation. The normal implementation time should be within 5 business days, but sometimes requests require a more immediate implementation. I also have a status column, column "B", that shows whether a request has been completed or is in some other state, such as "approved", "pending", "clarification required", etc. In any case where there is a "Y" in the Expedite column and the value in the status column doesn't equal "Completed", I want to make the background of the cell where the "Y" appears in the Expedite column red, so I can easily spot ones that I need to track closely to ensure they are completed by the given deadline for the request. I can easily do that with conditional formatting.

To apply that formatting, I can click on one of the cells in column "O", which is the "Expedite" column and then click on Format and select Conditional Formatting and set a formula for the cell. E.g., if I've selected the first row in the spreadsheet, I can use the formula below:

=AND(B1<>"Completed",O1="Y")

The formula specifies that if cell B1 doesn't contain the text "Completed" and cell O1 contains "Y", then the condition is met. I can click on the Format button and then pick the color I want to apply to the cell background, in this case I want it colored red. Conditions that form the logical conjunction are separated by commas.

Conditional Formatting - And

You can also apply conditional formatting with an "or" condition and copy the conditional formatting to other cells as explained there.

[/os/windows/office/excel] permanent link

Fri, Feb 13, 2015 4:50 pm

Countif greater than or equal to a date

If you want to determine the number of entries in a spreadsheet that have a date value that is greater than or equal to a particular date, you can use the COUNTIF function in Excel. E.g., if you had dates in column G rows 4 through 959 and wished to know the number of rows with a date that was greater than or equal to October 15, 2014, you could use the formula below, assuming you are using the common U.S. style of date in the form mm/dd/yyyy.

=COUNTIF(G4:G959,">=10/15/2014")

Suppose, instead, that you had a date in F970 and you wished to use whatever date you had in that cell for the date comparision operation. You could then use the following formula:

=COUNTIF(G4:G959,">="&F970)

The ampersand before the F970 concatenates the value in that cell to the string >=, so that if cell F970 contains the date value 10/15/2014, COUNTIF will only count cells containing a date greater than or equal to that date.

[/os/windows/office/excel] permanent link

Sun, Mar 23, 2014 2:08 pm

Julian date in spreadsheets

The ordinal date, i.e., the day of the year, is a number that ranges from 1 for January 1 through 365 or 366 for December 31, depending if the year is a leap year. The ordinal date is also referred to as the "Julian date", though in astronomy "Julian date" is not the ordinal date, but a serial date system starting on January 1, 4713 B.C.E.

If you wish to display the current ordinal date in a spreadsheet, such as a Microsoft Excel or a Google Sheets worksheet you can use the formula below:

=TEXT(TODAY() ,"yyyy")&TEXT((TODAY() -DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000")

That formula will insert the current day in Julian format with a four-digit year. E.g. for March 23, 2014, it would result in 2014082 appearing in the cell where the forumla is used, since March 23, 2014 is the 82nd day of 2014. If you just wish to display the day of the year and not the year, you can use the formula below:

=TEXT((TODAY() -DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000")

That forumula will display 082 in the cell in which the formula is used.

References:

  1. Insert Julian dates
    Support - Office.com

[/os/windows/office/excel] permanent link

Fri, Mar 14, 2014 10:30 pm

Excel countifs function

Microsoft Excel, starting with Excel 2007, provides a function, countifs, to count the number of occurrences of some item while stipulating multiple criteria be met. The function also is available in Microsoft® Excel® 2008 for Mac. The function works like the countif function, but whereas countif only permits one criteria to be stipulated, countifs supports one or more criteria. The syntax for countifs is:

COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

As an example, suppose I have the following worksheet in a spreadsheet:

<> A B C D
1 NumberProjectApprovedFunded
238397Alpha2/12/14N
338400Alpha2/7/14Y
438407Beta3/4/14N
538408Alpha2/25/14N
638409Epsilon3/14/14Y
738412Gamma3/14/14Y
838413Zeta3/14/14Y
938415Alpha3/14/14N
10    
11 Approved today 4   
12 Approved and funded today 3   

Number represents work request numbers associated with various projects. Column C has dates for when the work requests were approved with today's date being March 14, 2014. Column D indicates whether the requests have been funded with a "Y" for "yes" and a "N" for "no".

If I wished to count the number of work requests funded today, I could use the formula =COUNTIF(C2:C10,TODAY()) in cell B11. COUNTIF works because I have only one criteria. But, if I wanted to count the number that were approved and funded today, then I would need to use COUNTIFS rather than COUNTIF. I could use the formula =COUNTIFS(C2:C10,TODAY(),D2:D10,"Y") in cell B12.

[/os/windows/office/excel] permanent link

Once You Know, You Newegg AliExpress by Alibaba.com

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo