The Wiert Corner – irregular stream of stuff

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

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

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

    Join 1,531 other followers

Archive for the ‘Excel’ Category

VLOOKUP Example Spreadsheet & Tutorial | Productivity Portfolio

Posted by jpluimers on 2018/08/10

Because I tend to forget how to use this:

Excel VLOOKUP tutorial with an example spreadsheet & video. The article shows how to lookup the values from one column to use in another worksheet column.

Source: [Archive.isVLOOKUP Example Spreadsheet & Tutorial | Productivity Portfolio

–jeroen

Read the rest of this entry »

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

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 »

 
%d bloggers like this: