The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,715 other followers

Archive for the ‘Excel’ Category

Convert a text value into a date in Excel. Learn Microsoft Excel | Five Minute Lessons

Posted by jpluimers on 2017/01/09

To match dd/mm/yyyy and mm/dd/yyyy column values in columns C (Date1) and N (Date2), I used these formulas in row 2:

Meaning: Column: Formula: Explanation:
Date1Text I =TEXT(C2,”dd/mm/yyyy”) Interpret date text of the original text no matter the Excel settings
Date1Value J =DATE(RIGHT(I2,4),MID(I2,4,2),LEFT(I2,2)) Encode the date text to an actual date: dd#mm#yyyy format where # is any separator
DatesEqual K =J2=L2 Are the encoded dates equal?
Date2Value L =DATE(RIGHT(M2,4),LEFT(M2,2),MID(M2,4,2)) Encode the date text to an actual date: mm#dd#yyyy format where # is any separator
Date2Text M =TEXT(N2,”mm/dd/yyyy”) Interpret date text of the original text no matter the Excel settings

 

Learn how to convert text values into dates in Excel. This lesson covers a range of different scenarios to help you. The comments have even more examples.

Source: Convert a text value into a date in Excel. Learn Microsoft Excel | Five Minute Lessons

Posted in Excel, Office, Office 2007, Office 2010, Office 2013, Power User | 1 Comment »

datetime – Round time to nearest 15min interval in Excel – Stack Overflow

Posted by jpluimers on 2016/10/14

Easy, but I always forget:

= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440

Thanks David for answering.

–jeroen

via datetime – Round time to nearest 15min interval in Excel – Stack Overflow.

Posted in Excel, Office, Power User | Leave a Comment »

How to group by date in excel? – Super User

Posted by jpluimers on 2016/10/10

Sometimes you don’t recognize how easy it is until you see it:

Add a column that is of type date with value =FLOOR(A2,1) .Then Insert -> Pivot Table. Then drag the date column on the right into the Rows box, and also drag it into values box. Done.

Thanks David d C e Freitas!

via How to group by date in excel? – Super User.

Posted in Excel, Office, Power User | Leave a Comment »

How can I combine multiple nested Substitute functions in Excel? – Stack Overflow

Posted by jpluimers on 2015/11/20

You can’t: How can I combine multiple nested Substitute functions in Excel? – Stack Overflow.

Posted in Excel, Office, Office 2007, Office 2010, Office 2013, Power User | Leave a Comment »

How to aggregate (count/sum/average) cells and ignore the #div/0! ‘s – via: list of functions by Excel version

Posted by jpluimers on 2015/11/19

I bumped into a #DIV/0! result for average functions when processing large sets of data.

It is actually very easy to spot the error in small results, sets, but hard in big ones, as you cannot see the #DIV/0!

So there are average functions that can ignore certain outcomes. COUNT already does that (there is no COUNTIF), the others have a *IF equivalent, but not in all Excel versions:

Note there is a small SUMIF/SUMIFS/AVERAGEIF/AVERAGEIFS in Excel 2010 (not in 2007, and maybe not in 2013) glitch when the criteria are in a different sheet.

The seemingly easy workaround of summing columns A and B, then doing the division fails: it returns different results as it forgets to ignore faulty rows:

SUM/AVERAGE versus SUMIF/AVERAGEIF (click to enlarge)

SUM/AVERAGE versus SUMIF/AVERAGEIF (click to enlarge)

Leermomentje (techable moment comes close)…

–jeroen

via:

Posted in Development, Excel, Office, Office 2003, Office 2007, Office 2010, Office 2013, Power User, Software Development | 2 Comments »

 
%d bloggers like this: