passing date and time directly to a formula

[lezawang]

Hi
I want to add 1 day to a date. So I tried to write this formula

= 2/06/21 +1 (but I got 1.015873) - by the way my computer date is mm/dd/yy

Then I did this

="2/06/21" + 1 (i got 2/06/21)

So from the above it seems that excel take a date as a text if I want to pass it directly to a formula without using cell reference, am I right? If yes, why excel this time treat date as a text not number?

Also, the time I can not pass it directly, I have to use " "

= "10:00:00 am"+ 1

Thank you very much.

 

[Toadstool]

Hi Lezawang,

Your first example is treated as maths, 2 divided by 6 divided by 21 plus 1 so you get 1.015873.
For your second example I got 7 Feb 2021 because it added 1 to 6 Feb 2021 (Excel holds dates as integers since 1 Jan 1900 so it's actually holding 44234 but it knows it's a date so displays it as such).
The third example demonstrates how time is held as a fraction of a day so it returns 10am on 1 Jan 1900.

The safest way to work with dates and times is to use the inbuilt date and time functions, so DATE, TIME, and if you select Formulas and the Date & Time dropdown you'll see many more.

If I wanted to use a hardcoded date I'd use =DATE(2021,2,1) to make sure it was interpreted correctly.

The tricky part is understanding that what's on the screen is not necessarily what Excel is holding so here I show the actual value using VALUE. Excel also tries to pick a suitable default format, but that may not be what you want.

2.xlsx
	G	H	I
5	Format	Date &/or Time	VALUE
6	General	1.015873016	1.015873
7	Date dd-mmm-yy	07-Feb-21	44234
8	Date 3/13/12 1:30pm	1/1/00 10:00 AM	1.416667
9	Date *3/14/2021	2/2/2021	44229
10	Date *3/14/2021	2/2/2021	44229.42
11	Custom dd-mmm-yy h:mm	02-Feb-21 10:00	44229.42
Sheet1

Cell Formulas
Range	Formula
H6	H6	= 2/6/21 +1
I6:I11	I6	=VALUE(H6)
H7	H7	="2/06/21" + 1
H8	H8	= "10:00:00 am"+1
H9	H9	=DATE(2021,2,1)+1
H10:H11	H10	=DATE(2021,2,1)+1+(TIME(10,0,0))

 

[jasonb75]

If you enter 02/06/21 into a cell then it is a date, if you enter =02/06/21 into the same cell then it is no longer a date, but a mathematical calculation. The same would apply when the date is in a formula, there is no way for excel to know if it should be a calculation or a date so a consistent default has to be used.

Similarly with time entries, 10:15 can be a time or 6 rows in the sheet.

In both cases, using text entry nullifies the default behaviour, any numeric value entered into a formula as text will be treated as if it was entered into a separate cell for calculation purposes.

Excel also has DATE, DATEVALUE, TIME and TIMEVALUE functions for such purposes. While less likely with time, entering a date as "2/6/21" is not recommended, it is preferable to use the DATE function in order to prevent errors. For files that only you will use, there should be no problems, but for anything that is being used by other people, especially someone in another country, a date entered as text will not adjust to match local formatting, meaning that if I opened your file then the date, "02/06/21" would be evaluated as June 2nd, not Feb 6th.