Free Microsoft Excel 2013 Quick Reference

Excel Date/Year issue

Hello,

Just recently my computer was replaced as the old one cratered. I setup all my settings exactly as they were in my previous setup (regional, language). Same version of excel, however when I enter only the month & date only, it assumes the date is a year and sets it for the first of the month.

Example:

Entered: Aug 23
Expected: 23-Aug-2011
Result: 01-Aug-2023

It has never done this before. What am I missing guys?


Post your answer or comment

comments powered by Disqus
Excuse me if this question has been covered elsewhere as a search for it didn't turn up anything relating to this issue for me...

In Excel 2003, if I use the YEAR function nested with the DATE function, the solution is always incorrect. However, if I review the function in the Function Arguments 'popup' (by hitting the 'fx' button on the toolbar) the solution is actually correct.

E.G.

A1 = 1/1/2006

B1 = DATE(YEAR(A1),MONTH(A1)-6,1) {returns 7/1/2005}
however,
C1 = YEAR(DATE(YEAR(A1),MONTH(A1)-6,1)) {returns 1905}
and
D1 = YEAR(B1) {returns 2005}

AND

the formula editor 'Function Arguments' has C1 with a solution of 2005, but the formula result of 1905.

Can anyone clue me in on this?

Thanks (sorry if any of that is confusing)

I have a worksheet containing client data along with several dates(3 per column). About half of the dates are before 1900 so its not an issue, the others however, as you know, excel is storing them as a number, not a date, ex 39440 instead of 12/24/2007. My issue arises when I attempt to import this sheet in to Access. Access does not recognize the digits as a date, and will not import the data. Is there a workaround for this, or any way to keep Excel from storing the data as a number? Thanks for you help in advance!!!

Hello,

I am looking to add years and months to a date to obtain some final date. Like an expiration date.

However, I need it to factor in leap years which excel does not seem to do using the Date function unless I'm using it incorrectly.

I've created a sample of what I'm looking for using the expiration date example. Excel simply seems to add year to year, month to month, without considering leap years in between.

The format I am using is along the lines of:

=DATE(YEAR(A1+B1),MONTH(A1+B2),DAY(A1)) where A1 is the starting date and B1 and B2 are years and months respectively.

i did a search on this AWESOME BB (i likes it alot!!!) and the website too
i found a formula here that's 1/2 what i need but i'm trying to take a date and add 30 days the hard part (for me) is to create a function (i think an "IF" function) that when that 30th day falls on a weekend to automatically forward it to the previous Wednesday...
so far all i have is the
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+30)

this formula is good to calculate 30 days but if it falls on on a sat or sun i need it to be bumped BACK to wed...
i tried looking here and there and on excel's help but i'll just keep getting more confused...
so... i think i need a way to assign a numerical value to the days (i thought excel already did this but i can't get it to do this...)
i wanted to make it say that the weekend is greater than 5 and if it was then to set the day to the previous 3 which would in turn be wednesday...
am i on the right track?
did i overlook something?
thanks in advance...

:wave:

Hi
I trying to understand why Excel does the following to the date column A
It takes the date divider and canges it to a "/" from a "-" when the original dat file is opened using Excel as the application
The original dat file data is attached also, just below
Anyone have any thoughts on this? Would be greatly appreciated
Using Excel 2003

******** ******************** ************************************************************************>Microsoft Excel - 1789 Cav 1 May 08.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1383=
ABCDEFGHIJ138312/5/2008 23:49out137724162.997505200.05255.9138412/5/2008 23:50out137826162.987405250.05255.5138512/5/2008 23:51out137925162.997425320.05255.5138613-05-08 00:10:54bad13803091.943191590.0781175138713-05-08 00:11:50bad138128101.943192010.07756138813-05-08 00:12:45bad13823091.943222050.07856.1138913-05-08 00:13:42bad13833381.943132080.07856Data 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Original Dat data same range:

12-05-08 23:49:29 out 1377 24 16 2.99 750 520 0.052 55.9
12-05-08 23:50:23 out 1378 26 16 2.98 740 525 0.052 55.5
12-05-08 23:51:19 out 1379 25 16 2.99 742 532 0.052 55.5
13-05-08 00:10:54 bad 1380 30 9 1.94 319 159 0.078 1175.0
13-05-08 00:11:50 bad 1381 28 10 1.94 319 201 0.077 56.0
13-05-08 00:12:45 bad 1382 30 9 1.94 322 205 0.078 56.1
13-05-08 00:13:42 bad 1383 33 8 1.94 313 208 0.078 56.0

I have hundreds of dates in an excel file, which are in number format, like this for example:

190508

(which would be 19th May 2008. Excel thinks this is the number 190,508)

How could I change these to MS excel date format? Right now I'm having to go through each cell and adding in the "/" between the date, month and year.

All numbers have 5 or 6 digits - the first one OR two digits represent the date. The last four are always the month and year.

Thanks,

Any help will be appreciated.

I use an endless variation of the formula below in excel and would now like to use a form of it in VBA but I am not sure about the right syntax.

=Date(Year(Variable),Month(Variable) -1,Day(Variable))

I tried

application.worksheetfuntion.Date(Year(variable),Month(Variable)-1,day(Variable)) but I could not get that to work.

Thank You

Hi, someone sent me an excel file with the date field format *3/14/2001. She
is using XP, and on her screen, the date Oct 5, 2006 was displayed as
10/5/2006. However, when I opened the file in Excel 2003 (Windows 2000), the
date was displayed as 5/10/2006. I interpreted the date as May 10, 2006.
Another cowork who uses XP opened the same file was able to see the date as
10/5/2006.
What should we do so we all see the date displays as mm/dd/yyyy? Your help
is much appreciated.

Dear Experts:

I am confused to differnece between system date and excel Date ()
function.

Please difference the following:

1... =Date(year, month, day)

2... Simple typing in excel as July 10,2008,

3.. Window system date and Excel Date function

4.. Is there any link between the Date() function and system date,

I am confused to differnece between system date and excel Date () function.

Please difference the following:

1... =Date(year, month, day)

2... Simple typing in excel as July 10,2008,

3.. Window system date and Date function

4.. Is there any link between the Date() function and system date,

In need an excel function to convert the julian date format commonly used for
satellites, YYYYDDDHHMMSS (DDD is julian day of year) to Excel date time
format.

Hello everyone,

Anyone who could help me with the following Excel sum forumla issues will be my super uber Excel hero from now until eternity.

I need 2 different formulas to generate commission reporting information on the Summary tab of the attached sample Excel file.

The first is highlighted in green. For these cells, I need a sum formula that reports the total commissions (column H of the "Data" worksheet) for items Ordered in the month listed in column B of the "Summary" worksheet, but not invoiced until the month listed in the column D, E & F headers of the same worksheet. Date of item order can be found in column A of the "Data" worksheet. Date of invoice can be found in column E of the "Data" worksheet.

Now, the problem that I think I am going into is the way Excel handles dates and times. All columns and data highlighted in orange on the data sheet need to be maintained without being changed, as eventually I am going to have a report setup by our operating program drop in there so that it automates the information without any additional labor by our employees who have varying levels of Excel proficiency. Unfortunately, the report from our operating program cannot simply list a date without a time. Feel free to create any column or field to the right of the orange columns in order to complete formulas based on those orange columns. I will just lock those cells when finished so that coworkers don't accidentally blow the shizel up.

The second sum formula that I need is highlighted in yellow on the "Summary" worksheet. Basically, I need a formula that sums all commissions in column H of the "Data" worksheet for those items that are cancelled AFTER invoicing. Column D of the "Data" worksheet lists the cancellation date.

There are explanations for each of these on the worksheets for quick referral.

I have tried various formulas for these to no avail so any assistance is greatly appreciated. I'd prefer not to stab out my eyes in frustration.

Hello,

I would be grateful if someone was able to help me with a date conversion
issue.

I want to be able to convert an Excel date to date of my format. But
without sending or displaying anything in the spreadsheet. Once the date is
converted I want to display it in a ListView and will not be displayed within
spreadsheet.

So I would appreciate your help on this matter, I have looked on the web and
looked at VBA functions but no joy.

Many Thanks

Sunnous

Hi,

I am attempting to sort a list of companies that are formatted in our
database system by month/date/year (mm/dd/yyyy). I want them to sort by
month and date only, however Excel 2000 recognizes the year as a criteria.

How do I get the program to ignore the year information for these 4,000+
entries?

Thanks so much.
SMW820

I use Win XP Home Edition, SP2, Excel 2003

Can someone inform me how to put Month, date, year and day of the week in
cell. I'd like to insert for example, 05/18/06 Wed

Thanks to all beforehand. I am in need of excel formula or VBA Code to change date format from 05-30-2010 into 30-05-2010. Actually every month I received excel dump from different companies in which I received date format as given above as premium due date, I used different different excel formulas but not able to change its format into proper excel format. Kindly help me resolving this problem.

I used to use text to column then adding them using this formula =date(year(a1),month(a2),day(a3)). But rather I want formula or code for this solutions.

Excel sameple sheet is attached for reference.

Regards,
Pramod Kumar

Hi there,

I need to convert the following string into Excel dates:

Jan 1, 2009
Jan 12, 2009
Jan 21, 2009

Right clicking it and changing it to date format will not work.

I have tried extracting individual days/months/years out by using Left/MID and Right functions but as you can see, problem lies when there days with single digit or double digits.

Does anyone have a better solution?

Thanks

Excel stores its type Date (date/time) values as a serial number containing an integer part representing a date and a fraction part representing a time value. My question is, what are the units for an Excel date/time value?

Iím interested because I often use whatís called dimensional analysis to help me assemble a formula in Excel. Dimensional analysis requires you to substitute the units of each item in an equation or formula for the corresponding numeric value. This technique can help you decide whether to multiply or divide two items when it isnít apparent which you must do. It also helps determine if you need to add a constant like 60 minutes/hour to make things work out.

For example, you know the distance (in miles) that your car traveled on a trip, and you know how long it took to complete the trip (in hours). To determine the average speed the car traveled, you must divide one of those numbers by the other Ö but which one goes on the top?

Well you know that the end result has the units of speed (miles per hour or miles/hour). Thatís a strong clue that when you divide, the distance value should go on the top of the division and the time should go on the bottom. So the formula would be = miles / hour. Itís not always so obvious when you are trying to figure out a complex formula.

For this technique to work, you MUST know the units for ALL factors, In the problem above, the unit of distance was specified as miles and the unit of time traveled was specified as hours. Suppose that instead of recording time traveled as, say, 30 minutes, you typed into your Excel spreadsheet 0:30:00, which is the Excel date/time format for 30 minutes. In this case what would the units of that stored value be? No, itís not minutes. Reread the first sentence above about how Excel stores a date time value.

I have just about concluded that the unit of an Excel date/time value is ďdayĒ, but Iím unsure about that. Does anyone know for sure?

Excel 2007 formatting issues

When I try to save an excel file  to an older version I am losing all of my formatting in my spreadsheet.  Is there anything I can do to keep this from happening?  I have intalled all of my updates.

Hello,
I am using Excel VBA to read a binary file that was dumped by a VB.Net application. Among other things, the records contain a timestamp in DateTime format. Since VBA does not support this object, I have to read the time as a Double, or some other combination of datatypes totaling 8 bytes. Short of writing a VB DLL and linking it to the sheet, does anyone know of a way to convert the DateTime to an Excel Date ?
Thanks

Hello Excellers,

Dates are stored as "Fri Oct 12, 19:30 EDT 2007" in column A. The goal is to convert this string to excel date/time format in column B. Workin on it in VBA

I use an endless variety of of the function below and I would like to know the correct sytanx for writitng a similar formula in VBA.

=Date(Year(CellReference),Month(CellRefference)-1,Day(CellRefference))

I tried:
Application.worksheetfuntion.Date(Year(Variable),Month(Variable) -1,Day(Variable))

But got an error.

Thank you

I have an excel spreadsheet linked to a word document. The word document
justs posts numbers if I use a date format in excel. If I format excel as
text it displays the date as entered MM/DD/YY.

Is there a way to keep my excel date format and have word display date in
text and numbers, like"July 13,2005"

Hello readers,

I am trying to set up a reference list in Excel. I created columns with
author, title etcetera. One of the columns is date, in date format.
When I try to concatenate the whole reference into one cell with this
code (with the date in cell G2):

=C2&" "&D2&" "&E2&" "&F2&" "&G2

My date is displaced by an Excel date, i.e. 38360 for 8 jan 2005. How
can I replace the huge number by the actual date?

Thanks a lot,

Arjen


No luck finding an answer? You could always try Google.