Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-05-2016, 08:11 AM
markg2 markg2 is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2016
Expert
A basic date math/data view question
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default A basic date math/data view question

Start date, Today's date, Cumulative days between the two



To subtract Today from Start the date has to be reformatted to a number. Ordinary humans (me) find it difficult to readily grasp the #'s meaning.

So does that then entail double columns for each date--one formatted as a date to understand visually the date and the other as a number for the calculation?

Also, if I enter the keyboard shortcut for Today's date, save and then reopen the sheet the next day will said Today's date be automatically updated? But here's the clinker, that Today's date shortcut entered a visual date--I need a Today's date that the math will work with?

Mark
Reply With Quote
  #2  
Old 05-05-2016, 09:22 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Dates you see in Excel are only formatting. The value XL works with to do any operation is the underlying value ( number of days since Jan st 1900). So you shouldn't worry about these values. Just go ahead and perform operations on the dates

Yes, = TODAY() will give another result tomorrow, no, if entered with a kb shortcut it will not

Beware though, if what you see is a real date or text looking like a date ! The first one right aligns in the cell, the second left aligns as all text does.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 05-05-2016, 12:56 PM
markg2 markg2 is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2016
Expert
A basic date math/data view question
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default

Didn't work at this end. Here's what I did:

Start date cell: 9/2/2015, Current date cell: = TODAY() which became 5/5/2016, Cumulative days cell: +Current-Start equaled 9/2/1900?
Reply With Quote
  #4  
Old 05-06-2016, 12:25 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

The result being a number of days, you should format that cell as number.

To understand better how XL works with dates have a look at http://www.cpearson.com/excel/datetime.htm#AddingDates

( there are many other very good explanations on the web)

But don't forget, XL is not a WYSIWYG
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 05-06-2016, 08:37 AM
markg2 markg2 is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2016
Expert
A basic date math/data view question
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default

Well that was sure easy.

I double checked one of the answers against a web date calculator. I told the calculator to include the current date as a day in the subtraction which resulted in it's value being 1 day greater than XL.

I've searched Options and I (admit to having) scanned your help link (that I'm saving for reference) and don't see an available modification?
Reply With Quote
  #6  
Old 05-06-2016, 08:52 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Quote:
Originally Posted by markg2 View Post
I've searched Options and I (admit to having) scanned your help link (that I'm saving for reference) and don't see an available modification?
What do you mean?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 05-06-2016, 08:59 AM
markg2 markg2 is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2016
Expert
A basic date math/data view question
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default

There are two methods of adding/subtracting dates.

1. 5/6/16-5/1/16=5
2. 5/6/16-5/1/16=6

The second example you include the last date in value, the first you do not. The spreadsheet returns '5', the internet calculator when told to include the last day returns '6'.
Reply With Quote
  #8  
Old 05-06-2016, 09:07 AM
xor xor is offline A basic date math/data view question Windows 10 A basic date math/data view question Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

In the following I work with the format dd-mm-yyyy

With 02-09-2015 in A1 and
05-05-2016 in A2
then in A3 try to enter
=A2-A1 which I think returns 02-09-1900.

Now try to format cell A3 General. and you should get the number 246 which is the number of days from 02-09-2015 to 05-05-2016.
Reply With Quote
  #9  
Old 05-07-2016, 01:16 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

If you need to include one day, just add 1 to the difference
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #10  
Old 05-07-2016, 05:58 AM
markg2 markg2 is offline A basic date math/data view question Windows 7 64bit A basic date math/data view question Office 2016
Expert
A basic date math/data view question
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default

Admittedly focused on (to me) an 'exotic' solution ;-)

Thanks--

Mark
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Just Starting Out, Pretty Basic Question fishhead202 Word VBA 3 10-31-2014 05:56 PM
A simple math question markg2 Excel 6 03-01-2014 11:10 AM
Forms: A very basic question/request Coli Outlook 0 08-24-2011 11:14 AM
A basic question I'm sure. talosian PowerPoint 0 04-21-2011 05:43 AM
Auto-updating basic math functions & cross-referencing tables FranklinBluth Word Tables 13 11-19-2009 10:26 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:09 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft