All articles with 'date and time' Tag

As some of you know, I run an online Excel Training Program called as Excel School. I have opened registrations for 3rd batch of this program on September 14th. Thank you very much for supporting this program wildly. In a few hours, I will be closing the registrations for Excel School.

Click here to sign up for Excel School

Read the rest of the post to learn more about Excel School and also download time across world chart template.

This post builds on earlier discussion, How many hours did Johnny work? I recommend you to read that post too. Lets say you have 2 dates (with time) in cells A1 and A2 indicating starting and ending timestamps of an activity. And you want to calculate how many workings hours the task took. Further, lets […]

Today we have home work for you. You need to help Johnny figure out how many hours he worked on splitting atoms. He has crazy boss who is behind him for that timesheet. Now Johnny needs your help to write the correct formula so that he can tell his boss how many hours he worked and go home.

Go ahead and read rest of this post and tell your answers using comments. Go!

Few days back we have learned how to find if 2 sets of dates overlap using Excel Formulas. For eg. we have learned that to check whether {1 July,2010 to 23 July 2010} overlaps with the date range {15 July, 2010 to 16 August 2010}, we can use a formula like, =if(or(and(x>=a,x<=b),and(a>=x,a<=y)), “Overlap”,”Do not overlap”) […]

While preparing a project plan, I had a strange problem. I wanted to highlight all the project tasks that fall with-in a certain date range. At the lowest level, the problem is like this: There are 2 ranges of dates (a,b) and (x,y) and I want to know if they overlap (ie at least one […]

In yesterdays post – Find Quarterly Totals from Monthly Data, we have learned how to use SUMPRODUCT formula to find totals by Quarter from a set of monthly values. The approach is fine, but has one glaring draw back. It only works when you have data for one year. In that post, Martin said, … […]

Here is a problem we face very frequently. You have a list of values by months. And you want to find out the totals by Quarter. How do you go about it? There are 2 options: You can make a pivot report from the data and then group dates in that to find totals by […]

Often when we are making spreadsheets for forecasting or planning we would like to keep the starting month dynamic so that rest of the months in the plan can automatically rolled. Don’t understand? See this example: This type of setup is quite useful as it lets us change the starting month very easily. We can […]

This is continuation of our earlier post Exploring Pivot Table P&L Reports.

We have learned how to change our P&L report on various data elements. We have seen how the P&L report can be changed with just few clicks.

In this post we will be learning some grouping tricks in PivotTables. We will cover grouping of dates, text fields and numeric fields. You will need to start with Monthly P&L report prepared in previous post. We will also learn some really clever tricks and hacks on how to group data in Pivot Tables. So read on…,

Sometimes when we import data from another source in to excel, the dates are not imported properly. This can be due to any number of reasons. In this post, we will learn some tricks and ideas you can use to quickly convert text to dates.

Marko, who is a long time PHD reader and an excel ninja sent this via email, I work at an insurance company in Slovenia. At the beginning of each year we have a conversation with our superiors to review our work in the past year and to set new goals (main activities) that we’re gonna […]

If excel school were to be a bar, this post is your last call. Come one, come all and order the course now. Click here to sign-up for excel school (on a lighter note, if excel school were to be a pie, we wouldn’t be having this conversation :P) How many students have joined the […]

Last week I have reviewed Google’s flu trends chart and told you that is it is very well made. Out of curiosity I made a similar chart in Excel. In this post, I am going to share the experience and results with you. Interested? Read on…

Jennie, a sweet and ambitious lady set out to do 101 things in the next 1001 days. She took the inspiration from Day Zero Project. Not stopping there, she prepared a cute little excel sheet to keep track of all these new year resolutions and sent it to me.

I think this is a swell excel template if you want to keep track of your goals or new year resolutions or just manage a list.

This is second part of 2 part series on conditionally formatting dates in excel.

Highlighting Repeat Customers using Conditional FormattingIn yesterday’s post we have learned how to conditionally format dates using excel. In this article, you will learn how to use these conditional formatting tricks to highlight repeat customers in a list of sales records.