[techtalk] Excel/G-Sheets - Calculate Time Between Dates

Tue Jan 14 16:31:46 PST 2020

Greetings and welcome to this week’s issue of Tech-Talk!
Also introducing new tips on LEADERSHIP.
[http://r20.rs6.net/on.jsp?ca=f842a9a5-b40b-4a2e-9bdd-e6e259226606&a=1100778316270&c=980e0ed0-0713-11e6-a7b7-d4ae528ed502&ch=981cb4d0-0713-11e6-a7b7-d4ae528ed502]

[http://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

NEW: Leadership Tips in Tech-Talk

Hey, you asked for it … so they're here, starting now: Leadership Tips in Tech-Talk.

The request makes sense, right? After all, leading and communicating go hand in hand.
And because both require excellent tech skills it's easy to put all three together, making Tech-Talk a complete workplace resource for you.

Think about this ... leadership is for more than top management. That's right, it's for everyone. Anyone, in any role, can lead with ideas, influence the work environment to be more productive and positive and even head-up a project.

From now on Tech-Talk has three content areas: technology, communications and leadership. Be sure to SCROLL DOWN to the bottom of each newsletter for these every week. Also find the Leadership category in the Tech-Talk Database.

 This Week's Topic: EXCEL / Google SHEETS

1. VIDEO & ARTICLE ... Calculate Time Between Dates
2. COMMUNICATING ... "Tell me about that" yields gold
3. LEADERSHIP ... Announcing new ideas on being a leader at any level

[Calculate dates]<http://r20.rs6.net/tn.jsp?f=001aBCrHQAgBKJ-oNPcO_mmakXw_y8G3KrQdlMgSrpwJR7AW3U50aMgiPUMTzTNXtQa9P3o18kDMW43jXbpY7ovcjMVAcQiloV_r7FjX_b82RBj93NbnSsS_E-9dvxH9z6p4a03U7dQWD20n3_4Xcj-YO5YTprvtiSJ9c8CdmR-WxLW6JYNurY4tcx0GrQzmmR9YPj6zrbywJL3DGwyOWK9N36IA1wW_-ASXt3Qdfy2TfE=&c=QIYnDs2FrwN8TIYHKDCkX9B4nlqkBMR5ltvrtyzHi3QzmauJ0ch3xA==&ch=xYU9uzZby8mr0d12Q5A_WYmhUv37-TU4P4C_symL6-D_oBnogsAvSw==>

Photo by Djim Loic<http://r20.rs6.net/tn.jsp?f=001aBCrHQAgBKJ-oNPcO_mmakXw_y8G3KrQdlMgSrpwJR7AW3U50aMgiPUMTzTNXtQaiiRm0nTMhDluf3OH90z61etFx_VY-ZNAINYnXltDtrDyDxRccqXRwIqfY-QhHDO0-52-K_evw-yG1s4o9XxAiHsLXcQ-mAlCu-oyDOO7vUHMujPDCfazYoDOo-E8MVi80JHu7NScKSMrZjXjid6vjEgRVpUqOyGpqu1CwJHoYEdWv1M06n29KqYfSCwkAXIE&c=QIYnDs2FrwN8TIYHKDCkX9B4nlqkBMR5ltvrtyzHi3QzmauJ0ch3xA==&ch=xYU9uzZby8mr0d12Q5A_WYmhUv37-TU4P4C_symL6-D_oBnogsAvSw==> on Unsplash<http://r20.rs6.net/tn.jsp?f=001aBCrHQAgBKJ-oNPcO_mmakXw_y8G3KrQdlMgSrpwJR7AW3U50aMgiPUMTzTNXtQa3lDjc8ehj1_jhi9ZHaNoKlyrSp0HI2GU-rM3mr_zcrr3Q6BO6VGgaMiAHXnJgDIJhRt08tsJZ2a4SyQr7nETCY3A79ToLAcUIbyutkcE2O9BzQOdrh9cZ5cTbRq2D7_SWF7IQVPhptRJ8sako6v5CG3nl9u_JBJWEYSeXsnqKzYQIhhQHdNN32_K74lZGGXwg4kvsM1vId0=&c=QIYnDs2FrwN8TIYHKDCkX9B4nlqkBMR5ltvrtyzHi3QzmauJ0ch3xA==&ch=xYU9uzZby8mr0d12Q5A_WYmhUv37-TU4P4C_symL6-D_oBnogsAvSw==>

Brought To You By State Library of Oregon

[State of Oregon]

Visit Tech-Talk.com Database<http://r20.rs6.net/tn.jsp?f=001aBCrHQAgBKJ-oNPcO_mmakXw_y8G3KrQdlMgSrpwJR7AW3U50aMgiHhIRYkjyncgefLgYxe4JrTf4Xyra1wHkAnPGfk1Intv85N5dCN_MzORMJK4E9EOXDIvep1oPqtQaqfrZcC4Dltp5aSX_lvXCcfqALWFpnO4&c=QIYnDs2FrwN8TIYHKDCkX9B4nlqkBMR5ltvrtyzHi3QzmauJ0ch3xA==&ch=xYU9uzZby8mr0d12Q5A_WYmhUv37-TU4P4C_symL6-D_oBnogsAvSw==>

Username: ORLIBTECH
Password: ORLIBTECH

Questions about Tech-Talk?
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

EXCEL /Google SHEETS - Calculate Time Between Dates
Intermediate

I was trying to figure out how long each of our subscribers has been a member of Tech-Talk -- without counting the years and months on my fingers.

So I created a spreadsheet that contained the date that they began their subscriptions and "now" (today's date) ... because over time, the length of time between the dates will increase.

Fortunately, I found a great formula that automatically calculates the number of days (or months or years) of the memberships. Excel figured it out for me! (This also works in Google Sheets.)

When might you want to use the handy "today" formula?

·     Calculate how long someone has been a volunteer
·     Find out the average time a patron has been a card-holder
·     Determine the time spent on a a lengthy project

NOTE: You can also use the "today" function to automatically refresh a date within a report, a log, or a tracking document that you use on a regular basis. So any time you want to show the current date, use the "today" formula.

Calculate the Difference Between Two Dates

We are displaying this process in Excel, and the exact same formula works in Google Sheets as well.

In setting up your spreadsheet you need at a minimum:

1.  A column describing the item
2.  The starting date
3.  The ending date (or "today's date")
4.  The formula to calculate the time between the two dates

[https://files.constantcontact.com/ee1208b4001/7f15d21f-4ba7-4ac8-b213-8e3d5d6edd6e.png]

In our example, we have:

1) The Member Name in Column A

2) The Date they started their membership in Column B.

3) Then in Column C we could type in the current date. However, if we want it to be the exact date at any time the spreadsheet is opened, we use the "Today’s Date" function. This way the spreadsheet updates this field continually because it isn't a static month, day and year. It will change and show the current date each time we open the spreadsheet.

To add "Today’s Date" to a cell, the function is:

=TODAY()

[https://files.constantcontact.com/ee1208b4001/eef1b03a-443e-4eaf-80f9-214698ef7dc2.png]

Notice in the image above, all the dates in Column C are the same. (Of course because they are all "today's date.") But how can you quickly add that formula to all the cells in the column? It's easy. Click the lower right corner of the cell (you'll see a "+" sign), then drag it down the column. This copies the function in all the other cells.

4) The final step is to add the formula to calculate the number of Days, Months or Years between the two dates, which is the "DATEDIF" function (Date Difference).

In this example, we'll calculate the number of Days between the two dates. Putting the formula in a new cell, type:

=DATEDIF(B2,C2,"D")

You'll substitute the cell reference B2 & C2 with the two columns you're calculating. And if you want Months (M) or Years (Y) instead of Days (D) change the letter in the formula.

TIP: If you input these functions and they do not display correctly, the cell formatting may need to be adjusted. Right-click on the cell, choose Format Cells, and pick General (or Number).

[https://files.constantcontact.com/ee1208b4001/cf7ecfd9-a296-4e38-aaf3-f8d89a4699e0.png]

To calculate the number of Months between the two dates, in a new cell, type:

=DATEDIF(B2,C2,"M")
(substitute the cell reference B2 & C2 with your own)

[https://files.constantcontact.com/ee1208b4001/24de54cc-f6c0-4871-a466-bb4d35e54f92.png]

To calculate the number of Years between the two dates, in a new cell, type:

=DATEDIF(B2,C2,"Y")
(substitute the cell reference B2 & C2 with your own)

[https://files.constantcontact.com/ee1208b4001/f3fa7591-7c5d-48b6-85ec-2a90419b5d18.png]

If You Use Google Sheets...

Again, these formulas (Today's Date and DATEDIF) work exactly the same in Google Sheets as outlined above.

[https://files.constantcontact.com/ee1208b4001/f6fa5942-9893-4dd8-aa31-4c44041be5de.png]

[http://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communications: Talking
"Tell me about that" yields gold.

One of the most effective phrases I use more and more is one which really opens up a conversation ... and reveals insight into the core issue.

It's simple and can be used in many different situations.

The phrase is, "Tell me about that."

A variation is, "Tell me more."

Here are a few sentences to get you thinking about when you can use these magic phrases. Considering each situation you may use a different tone with the words.

·     Someone makes a comment that confuses you. ("What makes you say that?" or "Tell me more." - to gain clarity.)

·     You're at a loss for words, but need to say something. ("What makes you say that?" or "Tell me more." - to buy time.)

·     You don't fully comprehend and need more information. ("What makes you say that?" or "Tell me more." - to get additional data.)

·     You hear something that will require sensitivity on your part; you want to prepare what you want to say. ("What makes you say that?" or "Tell me more." - to give you a chance to come up with a good response.)

[https://files.constantcontact.com/ee1208b4001/da1c6ee9-ba35-4af5-9c80-8e1b5781189e.png]

Leadership Thoughts and Techniques
#1: A leadership book, in serialized form

Let me introduce you to a pocketbook that I wrote several years ago ... and which we are sharing with you now ... slightly modified to speak to everyone.

We're doing this because, as Tech-Talk readers, you've requested more ideas on leadership.

The book is called ...

"7 Habits of Egalitarian Managers, Employee Engagement"

As you can tell from the title, it was originally written for managers, but we have modified it for you because the principles are universal … and useful.

We've created a serialized version so that you'll get fresh bits, each week, in numerical order, until it's completed. If you miss one, you can find every issue in the Tech-Talk Database.

To give you a quick overview of this series … and generate a little curiosity … here's what you can expect (table of contents).

[https://files.constantcontact.com/ee1208b4001/1db7eea5-6ae2-4819-87fd-e90e4537b986.png]

The book starts with this quote from Colin Powell.

Leadership is solving problems.
The day people stop bringing you their problems
is the day you have stopped leading them.
They have either lost confidence that you can help
or concluded you do not care. Either case is a failure of leadership.

You can solve problems. You can have the confidence of others. You can lead others.

Ask Yourself ...
"How easy is it for people to bring their problems to me?"

To your leading in many ways,

[https://files.constantcontact.com/ee1208b4001/47f66310-369f-45fb-b43d-e7ff611743e7.jpg]

Shared Results International<http://r20.rs6.net/tn.jsp?f=001aBCrHQAgBKJ-oNPcO_mmakXw_y8G3KrQdlMgSrpwJR7AW3U50aMgiPUMTzTNXtQa_6FRuk3LdHse0xE8xENLiAzdq6Y8-OuCwAJIPlBmLdT39cWV8MfqxPNev05cZFhEoZf-K-WokttXQtiZaCdz434Ij1GBvRGuZli5m5a9NQthdrEpXgeD1vUWjwO_0L_hadL5mOJ-Wy64rzZushVnfMBgzHUUK45fKKCMHAX4md4=&c=QIYnDs2FrwN8TIYHKDCkX9B4nlqkBMR5ltvrtyzHi3QzmauJ0ch3xA==&ch=xYU9uzZby8mr0d12Q5A_WYmhUv37-TU4P4C_symL6-D_oBnogsAvSw==>

[http://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<http://r20.rs6.net/tn.jsp?f=001aBCrHQAgBKJ-oNPcO_mmakXw_y8G3KrQdlMgSrpwJR7AW3U50aMgiGfQwi8GpJCe3-t6apFDM89I_L776MIKYLhEmRRWUdCCS1uyPT995mDqSsn5q4xosq37QKuZkploz0J29DCd_INnJXg57wRBmUJVJbEn6zG5&c=QIYnDs2FrwN8TIYHKDCkX9B4nlqkBMR5ltvrtyzHi3QzmauJ0ch3xA==&ch=xYU9uzZby8mr0d12Q5A_WYmhUv37-TU4P4C_symL6-D_oBnogsAvSw==>

[Ask a question]<http://r20.rs6.net/tn.jsp?f=001aBCrHQAgBKJ-oNPcO_mmakXw_y8G3KrQdlMgSrpwJR7AW3U50aMgiJL5bRzjYXSj46WGbtX1eu1Qy6oc_YUY8kgFrpYyUzeL3arAf7BqVIvbxOaYAQQVugwF6_4Dyoso7jqwAjseZyTeZ6X6EQOCk-O-7UROctUpsmTNmcY8t9c=&c=QIYnDs2FrwN8TIYHKDCkX9B4nlqkBMR5ltvrtyzHi3QzmauJ0ch3xA==&ch=xYU9uzZby8mr0d12Q5A_WYmhUv37-TU4P4C_symL6-D_oBnogsAvSw==>

Copyright 1996-2020 Shared Results International. Published weekly. Do not forward this issue of Tech-Talk.com without prior permission from Shared Results International. Distribution is limited by contract. Forwarding it to unauthorized recipients constitutes copyright infringement. For information on site licenses or how to include additional recipients, call 941-355-2092.

The Tech-Talk e-newsletter for Oregon library staff is distributed weekly via the Libs-OR and TechTalk mailing lists. Know someone who might be interested in receiving a copy but they’re not on TechTalk or Libs-OR mailing lists? Have them contact darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> or visit http://listsmart.osl.state.or.us/mailman/listinfo/techtalk to subscribe to just the Tech-Talk newsletter!

Tech-Talk is a paid subscription service for staff of Oregon libraries and is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.

Cheers!
Darci Hanning, MLIS
Public Library Consultant / CE Coordinator
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> | 503-378-2527| www.oregon.gov/library<https://www.oregon.gov/library>
Follow us: Facebook<http://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Tumblr<http://www.statelibraryor.tumblr.com/> | Pinterest<https://pinterest.com/statelibraryor/>

[cid:image005.png at 01D31C27.4B9FEA30]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20200115/2e26c35d/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20200115/2e26c35d/attachment.png>