Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-19-2021, 02:32 AM
otuatail otuatail is offline Date Diffrence Windows 7 64bit Date Diffrence Office 2016
Competent Performer
Date Diffrence
 
Join Date: Jun 2012
Posts: 246
otuatail is on a distinguished road
Default Date Diffrence

Hi acording to Excell 2016 the number of days between 18th October and 19th November is only one day [Cell K3]


Where have I gone wrong?
Attached Files
File Type: xlsx Email Move.xlsx (8.8 KB, 10 views)
Reply With Quote
  #2  
Old 11-19-2021, 06:32 AM
wiganken wiganken is offline Date Diffrence Windows 10 Date Diffrence Office 2019
Expert
 
Join Date: Jul 2018
Posts: 279
wiganken will become famous soon enough
Default

Spreadsheet only shows column 'A' containing data on my machine. All other cells are blank so nothing in cell K3.
Reply With Quote
  #3  
Old 11-20-2021, 05:23 AM
otuatail otuatail is offline Date Diffrence Windows 7 64bit Date Diffrence Office 2016
Competent Performer
Date Diffrence
 
Join Date: Jun 2012
Posts: 246
otuatail is on a distinguished road
Default

No. Cell A2 has a date 18th October, Cell A3 has a date 19th November.


Cell K3 has =TEXT(A3-A2,"dd")
Reply With Quote
  #4  
Old 11-20-2021, 06:01 AM
p45cal's Avatar
p45cal p45cal is offline Date Diffrence Windows 10 Date Diffrence Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by otuatail View Post
No. Cell A2 has a date 18th October, Cell A3 has a date 19th November.
You really ought to check:
Attached Images
File Type: png 2021-11-20_125845.png (15.2 KB, 18 views)
Reply With Quote
  #5  
Old 11-20-2021, 09:00 AM
wiganken wiganken is offline Date Diffrence Windows 10 Date Diffrence Office 2019
Expert
 
Join Date: Jul 2018
Posts: 279
wiganken will become famous soon enough
Default

I think the OP has uploaded the wrong file.
Reply With Quote
  #6  
Old 11-20-2021, 09:35 AM
p45cal's Avatar
p45cal p45cal is offline Date Diffrence Windows 10 Date Diffrence Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by wiganken View Post
I think the OP has uploaded the wrong file.
Of course he has.

He should be trying
=A3-A2
for the number of days.

If he'd used
=TEXT(A3-A2,"dd/mm/yyyy")
he'd have got
01/02/1900

he used

=TEXT(A3-A2,"dd")
so he only saw the red portion.
Reply With Quote
  #7  
Old 11-20-2021, 01:55 PM
otuatail otuatail is offline Date Diffrence Windows 7 64bit Date Diffrence Office 2016
Competent Performer
Date Diffrence
 
Join Date: Jun 2012
Posts: 246
otuatail is on a distinguished road
Default

Sorry. I put up the wrong spread sheet.This is what I should have put up.


What - have is 19/11/2021 - 18/10/2021 =TEXT(A3-A2,"dd")
the dd part should be 32 days.
Attached Files
File Type: xlsx Water_To_2021-03-31.xlsx (36.1 KB, 7 views)
Reply With Quote
  #8  
Old 11-20-2021, 03:47 PM
p45cal's Avatar
p45cal p45cal is offline Date Diffrence Windows 10 Date Diffrence Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

See msg#6.

As an aside, you have many formulae with SUM in, most of which are unnecessary:
=SUM(D3*$A$21)/100
can be:
=D3*$A$21/100

=SUM(D3*$A$22)/100*0.95
can be:
=D3*$A$22/100*0.95

=SUM(C3/K3)
becomes:
=C3/K3

=SUM(E3+F3)
can be either:
=E3+F3
or:
=SUM(E3,F3)
or:
=SUM(E3:F3)

=SUM((B3-B2)/L3)
goes to:
=(B3-B2)/L3
Reply With Quote
  #9  
Old 11-21-2021, 12:57 AM
wiganken wiganken is offline Date Diffrence Windows 10 Date Diffrence Office 2019
Expert
 
Join Date: Jul 2018
Posts: 279
wiganken will become famous soon enough
Default

It's much easier to type =A3-A2 in cell K3
Reply With Quote
  #10  
Old 11-21-2021, 04:05 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Date Diffrence Windows 7 64bit Date Diffrence Office 2010
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

Your formula 19/11/2021 - 18/10/2021 =TEXT(A3-A2,"dd") is correct but it returns the day on which day 32 starting 01/01/1900.
Using a text function to return dates might get you in trouble for later analysis. much easier to use A3-A2 and format as suggested. The underlying value will not change and stay a number
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting date, month & year from a text string without using date format Help Haha88 Excel 5 08-06-2021 01:19 PM
Use Original Award Date or Mod Award Date For Date-Based Recurring Tasks KBCasey27 Project 1 04-30-2021 12:22 AM
Change font color if date in column F is prior to today's date. kevinbradley57 Excel Programming 1 12-05-2018 07:35 PM
Microsoft Project 2016 print specific date range stuck on one date martinsalmon Project 1 10-12-2016 11:36 AM
Excel pivot table with a DATE value field for some reason stops at a certain date angie450 Excel Programming 2 08-19-2014 08:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:39 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