In Tips and Tricks 138, we covered conversion from Excel Dates to Julian Dates. Here, we want to look at reverse. For 7 Digits Julian Dates, following formula should be used =DATE(LEFT(A1,4),1,RIGHT(A1,3)) For 5 Digits Julian Dates, following formula should be used depending upon which century (Note – Julian dates are most likely to fall…
Category: Tips and Tricks
Tips & Tricks 138 – Convert from Excel Date (Gregorian Date) to Julian Date
Q. First what is a Julian Date? A. A Julian date has either 7 digits or 5 digits date and these are generally used in old IT legacy systems. 7 Digits – YYYYDDD – 2016092 (This is 1-Apr-2016. 92 means that this is 92nd day from 1-Jan in that year) 5 Digits – YYDDD –…
Tips & Tricks 137 – VBA – Delete Blank Rows for a Range Quickly (without Looping)
Suppose, you have data like this and you want to delete rows 3,4,6,8,11 & 12 as they are blanks. You need not loop through all cells but you can perform this operation is one shot. You can use below code for the same On Error Resume Next Range("A1:A13").SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0
Tips & Tricks 136 – Quickly Copy a Formula or a Value in a Large Number of Cells
Say, you have written a formula (or put a value) in cell B1 and you want to fill in this formula till B10000. You can drag the formula but higher the number of cells, more tedious it becomes. Fortunately, we have a way to do this very fast. 1. Put formula in B1. 2. CTRL+C…
Tips & Tricks 135 – I input (c) or (C) and it gets converted to Copyright Symbol..I don't want this
There are many ways to overcome this. 1. a. Put following in a cell and press Enter c) b. Go back to the cell and type first bracket to complete (c) 2. a. You can type (c) preceded with some special character and press enter. b. Go back to that cell and delete that special…
Tips & Tricks 134 – Last Working Day of the Year
If a year is given in A1 say 2016, below formula can be used to know the last working day of the year (format the result as date) =WORKDAY("1JAN"&A1+1,-1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula…
Tips & Tricks 133 – First Working Day of the Year
If a year is given in A1 say 2016, below formula can be used to know the first working day of the year (format the result as date) =WORKDAY(EOMONTH("1JAN"&A1,-1),1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula…
Tips & Tricks 132 – Financial Year Formula (e.g. 2015-16 or FY16)
A good number of countries don't follow calendar year as the financial year. For example, India's financial year start is 1-Apr and finishes on 31-Mar. Hence, currently (20-Feb-16), the financial year is 2015-16 (It is also written as FY16). On 1-Apr-16, it will become 2016-17 (It is also written as FY17). Now if a date…
Tips & Tricks 131 – Repeat a Number and Increment and Repeat….
Suppose, you have been given the task of repeating a number and increment that number and repeat it. For example – 1,1,1,1,2,2,2,2,3,3,3,3…..(Here, we are repeating it 4 times and incrementing and repeating 4 times again and so on) Then you can use following formula =ROUNDUP(ROWS($1:1)/4,0) Suppose, you want to start the number with 5 not…
Tips & Tricks 130 – Generate Sequential Numbers and Repeat them
Suppose, you have been given the task to generate a sequence of numbers and repeat them. For example – 1,2,3,4,1,2,3,4,1,2,3,4 You can use the below formula and drag down – =MOD(ROWS($1:1)-1,4)+1 Replace 4 with with any other number to generate any other sequence. Hence, if you want to generate 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10 then formula becomes – =MOD(ROWS($1:1)-1,10)+1…