UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Number Os Day's Beetween 2 Dates... VBA, Office 2013    
 
   
Miguel_A
post Jan 1 2018, 06:45 PM
Post#1



Posts: 232
Joined: 20-January 04



Hi guys

Why does cell B3 ("monthDays") retrieves 31 and cell B4 ("Day(monthDays)") retrieves 30 ?

If the difference between the 2 dates is 31 day's it should both retrieve 31 don't they ?

CODE
Sub WorkCalendar()
dateStart = DateSerial(Year(Date), 1, 1)
dateEnd = DateSerial(Year(Date), Month(dateStart) + 1, 1)
monthDays = dateEnd - dateStart
Cells(3, 2).Value = monthDays
Cells(3, 3).Value = Day(monthDays)
End Sub


Thanks in advance
Go to the top of the page
 
GroverParkGeorge
post Jan 1 2018, 10:50 PM
Post#2


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


A few questions to clarify.

FIrst, does this sub even compile? Doesn't it raise errors when you try to compile it?

What datatypes do you want to assign to the variables?
This post has been edited by GroverParkGeorge: Jan 1 2018, 10:52 PM
Go to the top of the page
 
GroverParkGeorge
post Jan 1 2018, 10:57 PM
Post#3


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


Why do you first do the math to count the number of days between two dates:

monthDays = dateEnd - dateStart

which returns 31.

And then try to turn that into a Day? That doesn't make sense. What Day value do you expect to get from the number 31?

Day(monthDays)

Or in this sample:

Day(31)
Go to the top of the page
 
Miguel_A
post Jan 1 2018, 11:26 PM
Post#4



Posts: 232
Joined: 20-January 04



Hi

My question is not about a error or something ...
It's just a question to understand why both cell's do not retrieve the same value ...

Why does monthDays and Day(monthDays) don't retrieve both 31 day's ?

Thanks in advance
Go to the top of the page
 
GroverParkGeorge
post Jan 2 2018, 12:20 AM
Post#5


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


As I pointed out, you are calculating a number of days between two dates and storing that NUMBER, 31, in the variable called monthDays.

Then you are trying to calculate a specific day of the month, from that NUMBER, not from a valid date. It makes no sense to do that. What do you expect to get from Day(31)? Day() is used, in Excel, to return the Day part of a Date, is it not?

BTW: ignoring errors isn't a particularly good programming practice, even if it's just a matter of compiling your code.
Go to the top of the page
 
Miguel_A
post Jan 2 2018, 02:49 AM
Post#6



Posts: 232
Joined: 20-January 04



Hi

Not from a valid date ... humm why not valid ?

Both calculated dates are already in a valid format so when making some kind of math between should give already a valid date ...

Exact i am storing the number of Days between two dates and in this particular case is it to know the number of day's a month has. However the issue is that the calculated number of day's is giving a different value from the last day of month...

The number of day's between 2018/2/1 to 2018/1/1 aka "monthDays = dateEnd - dateStart"
is equal to 31 day's aka "monthDays"
So it should be equal to the last day number of that particular month (the 31 january) aka "Day(monthDays)" ... However this one is giving 30 and not 31 ...

If both were not valid dates it would give some kind of error but it's not giving any error ... Is it instead giving at Cells(3, 2).Value = 31 and Cells(3, 3).Value = 30, being the first one correct and the second one incorrect when both value should give 31 cause from...

Making a more profound math...
dateEnd = 2018/2/1 at 00:00
dateStart = 2018/1/1 at 00:00
monthDays = dateEnd - dateStart = 1900/1/31 at 00:00
When using the Day(monthDays) it should give the 31 from 1900 of January, however it's giving 30 (for some odd reason)

My question remains cause i can't understand why the second cell is giving 30 and not 31 ...

Thanks in advance
Go to the top of the page
 
cheekybuddha
post Jan 2 2018, 06:28 AM
Post#7


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


Hi Miguel,

>> My question remains cause i can't understand why the second cell is giving 30 and not 31 ... <<

Your problem is that you have misunderstood what the Day() function returns! See here:
MSDN - Day() function

QUOTE
Description

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.


>> When using the Day(monthDays) it should give the 31 from 1900 of January, however it's giving 30 (for some odd reason) <<
No, it shouldn't!

So, you are trying to find the day of 'date' = 31.

Excel assumes you know how to use the Day() function correctly, so assumes that 31 must be a valid date, and not just an integer representing some other value.

It coerces 31 into a date. First you need to understand how dates are stored in computers. The are normally stored as the numbers of days since a special magic start date - time forms the fractional part of the number.

Due to the vagaries of computer spreadsheet program history, Excel's magic start date is 31/12/1899.

When Excel converts the number 31 to a date it adds 31 days to 31/12/1899, which results in: 30/01/1900

When you run the Day() function on 30/01/1900 it returns the day of the date = 30.

To test the conversions, do to the Immediate Window (Ctrl+G) and try out the following, hitting Enter after each line:
CODE
?CDate(1)        ' Find the original start date
?CDate(31)       ' See how 31 is represented as a date
?CLng(Now)       ' See how today's date is represented as a number - convert to Long Integer
?CDbl(Now)       ' See how  today's date AND time is represented as a number - convert to Double


So, the answer to your question is that you are using the wrong function for the job.

If you want to use a function to calculate the difference between two dates in Excel, have a look here

hth,

d
Go to the top of the page
 
GroverParkGeorge
post Jan 2 2018, 08:27 AM
Post#8


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


Thanks, David, for taking the time to explain the problem in detail.
Go to the top of the page
 
Miguel_A
post Jan 2 2018, 11:49 AM
Post#9



Posts: 232
Joined: 20-January 04



Hi

Thanks for replying, however i still have one questions regarding this, that in my perspective something is not entirely right or not entirely well done and therefor confuses me

Why when subtracting both dates (dateEnd - dateStart) it gives a value but if we sum them it will give a date ?
In my perspective it does not make much sense that just because it has a different signal it will give a different value format ...
If both dates are valid ones, sum or subtracting them it should always give the same format result .

Thanks for the help
Go to the top of the page
 
cheekybuddha
post Jan 2 2018, 12:05 PM
Post#10


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


Remember, the computer stores dates as a number.

The format you see is just a format applied by the computer to that number.

Try the following in the Immediate Window (Ctrl+G)
CODE
?#2018-01-30# - 10            ' Subtract 10 days from a date
20/01/2018
?#2018-01-30# - #2018-01-20#  ' Subtract 2 dates - return the difference in days
10
?CLng(#2018-01-30#)           ' View the numeric representation of a date
43130
?43130 - 10                   ' Subtract 10 from that number
43120
?CDate(43120)                 ' View the result as a date
20/01/2018


Any clearer?
Go to the top of the page
 
cheekybuddha
post Jan 2 2018, 12:53 PM
Post#11


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


>> Why when subtracting both dates (dateEnd - dateStart) it gives a value but if we sum them it will give a date ? <<

It doesn't - it depends on the formatting of your cells.

EG.
Cell A1: 01/01/2018
Cell B1: 03/01/2018
Cell C1: =A1+B1

The result in C1 is: 86204
Or if you format the cell as a date: 06/01/2136

So, it treats the value of A1 as 43101 and B1 as 43103

hth,

d
Go to the top of the page
 
PhilS
post Jan 2 2018, 12:54 PM
Post#12



Posts: 509
Joined: 26-May 15
From: The middle of Germany


QUOTE
Why when subtracting both dates (dateEnd - dateStart) it gives a value but if we sum them it will give a date ?

Internally dates are stored as floating point numbers. The integer part is the number of days since 12/30/1899, the decimal part is the fraction of the day.
So, subtracting two dates will result in the difference between them in days (and potentially fractions of a day). If you add both values it will result in a huge number of days since 12/30/1899. - You still could convert that back to a date, but it probably makes not a lot of sense.

As further reference and explanation, here's a bit on the internal storage of dates I've written recently.
This post has been edited by PhilS: Jan 2 2018, 12:55 PM
Go to the top of the page
 
cheekybuddha
post Jan 2 2018, 12:54 PM
Post#13


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


@George - no worries! Happy New Year wavehi.gif
Go to the top of the page
 
cheekybuddha
post Jan 2 2018, 04:37 PM
Post#14


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


@Phil - yet another top write-up! Keep up the great work! thumbup.gif

d
Go to the top of the page
 
Miguel_A
post Jan 2 2018, 08:23 PM
Post#15



Posts: 232
Joined: 20-January 04



Hi

Sorry if i was not clear ... I was speaking about in VBA ...

CODE
Sub DateCalculator()
dateStart = DateSerial(2018, 1, 1)
dateEnd = DateSerial(2019, 2, 1)
monthDaysSub = dateEnd - dateStart
monthDaysSum = dateEnd + dateStart
MsgBox ("Subtract is = " & monthDaysSub & " Sum is = " & monthDaysSum)
End Sub


If you guys test that code you will noted that will return different result types. One being a number and the other being a date.
Yehh if i do it in excel i need to pay attention to the format the cell has and for that reason it might give a very different result. however in VBA it's more accurate (i hope/think),

Has PhilS mentioned dates are stored in a floating point number so subtracting or summing the values should always give the same data type and unless i am doing something wrote with the example above...

Note: This is not part of any issue that i am having atm... The issue that made me create this topic was already resolve, however when messing with the calculations, this format result happen, made me a little curious about the why it's like that.

Thanks in advance for your patient and sorry for any mistake or misspell in english .
This post has been edited by Miguel_A: Jan 2 2018, 08:24 PM
Go to the top of the page
 
cheekybuddha
post Jan 2 2018, 08:54 PM
Post#16


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


The issue here is that you have not declared your variables, so they all default to variants.

Variants can hold any type of datatype and VBA has to make an 'intelligent' guess as to what it thinks you want.

See this amendment to your code:
CODE
Sub DateCalculator()

Dim dateStart As Date, dateEnd As Date, monthDaysSub As Double, monthDaysSum As Double
'Dim dateStart As Variant, dateEnd As Variant, monthDaysSub As Variant, monthDaysSum As Variant

dateStart = DateSerial(2018, 1, 1)
dateEnd = DateSerial(2019, 2, 1)
monthDaysSub = dateEnd - dateStart
monthDaysSum = dateEnd + dateStart
MsgBox ("Subtract is = " & monthDaysSub & " Sum is = " & monthDaysSum)

End Sub

Try first like it is, then try but comment out the first Dim statement and un-comment the second.

Please then look at Option Explicit declaration which you should use in ALL your VBA code modules - it will really make your life easier!

>> however in VBA it's more accurate (i hope/think) <<
Not if you don't declare your variables properly!!! laugh.gif

hth,

d
Go to the top of the page
 
Miguel_A
post Jan 2 2018, 09:48 PM
Post#17



Posts: 232
Joined: 20-January 04



Hi

Ohhh declare variables lol, it's very rare i do that ...
Yehh i guess that is why it's so dam important to do it, but for some reason i could never understood very well what types to use, so now i don't even try to declare them with some rare exceptions (when it's a string) ...

Yehh i got it now ... VBA is not a wizard ...

Thanks for all the explanations guys ...

Ohhh and happy new year
Go to the top of the page
 
cheekybuddha
post Jan 3 2018, 06:49 AM
Post#18


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


Haha! That is like trying to drive a car without knowing how to use the gears or indicators!

Quick start:
A variable to hold:
any text value: use STRING
any date or time value: use DATE
any whole number (no fraction):
- use BYTE (if it will be less than 255 or greater than -255)
- use INTEGER (if between -32,768 to 32,767)
- use LONG (if between -2,147,483,648 to 2,147,483,647)
any number with fraction:
- use SINGLE or DOUBLE
- use CURRENCY (if dealing with money)

You can then look at other datatypes once you have got a hold of those. Such as:
Variant
Object
LongLong
LongPtr
( ... and more!)

Happy New Year,

thumbup.gif

d
Go to the top of the page
 
GroverParkGeorge
post Jan 3 2018, 07:43 AM
Post#19


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


I would agree with everything Dave listed, albeit with a couple of small exceptions.

One, I almost never bother with Short numbers anymore. Dimming integers as Long will always be okay and it's highly unlikely you'll encounter problems because of that. Modern computers are pretty robust with substantial memory and speed.

Second, Currency is a good choice for many situations that don't necessarily involve money. Where you need to avoid problems with the errors that can creep into calculations based on double and single numbers, currency is a good alternative.
Go to the top of the page
 
cheekybuddha
post Jan 4 2018, 08:23 AM
Post#20


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


Good points, George!

I only included byte, single and double so the OP might learn what they are.

I too rarely use them, if ever, any more.

thumbup.gif

d
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 10:00 AM