#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Spreadsheet only shows column 'A' containing data on my machine. All other cells are blank so nothing in cell K3.
|
#3
|
|||
|
|||
No. Cell A2 has a date 18th October, Cell A3 has a date 19th November.
Cell K3 has =TEXT(A3-A2,"dd") |
#4
|
||||
|
||||
You really ought to check:
|
#5
|
|||
|
|||
I think the OP has uploaded the wrong file.
|
#6
|
||||
|
||||
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. |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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 |
#9
|
|||
|
|||
It's much easier to type =A3-A2 in cell K3
|
#10
|
||||
|
||||
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 |
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 |