CODEWITHSUNDEEP
exceldate
dave111
dave111

Reputation: 305

Excel adding dates to specific date format

I have dates coming out of a database into a column that have this format:

Column K AUG-14-2015 08:31:32 AM AUG-12-2015 06:10:03 PM AUG-12-2015 05:17:51 PM

I want to add like '2.2' days to each of these and put them in Column L. I have formatted the column in this format:

mmm-dd-yyyy hh:mm:ss AM/PM

I wasn't sure if the dashes are causing the problem, but when I do a =K15+2.2 I get a !VALUE. Any ideas what I need to do? Thanks.

Upvotes: 1

Views: 96

Answers (2)

Clifford
Clifford

Reputation: 93476

After some experimenting, it seems that while you can specify a date format "mmm-dd-yyyy hh:mm:ss AM/PM", the actual entry into the cells must be either a date in the system wide locale specific format, or a number of days since 1 January 1900.

So for example when you set a cell's format to custom:

"mmm-dd-yyyy hh:mm:ss AM/PM"

In my case (Windows 10 default UK locale settings, Excel 2010) for AUG-12-2015 06:10:03 PM, I can enter any of:

  • 12/08/2015 06:10:03 PM
  • 12/08/2015 18:10:03
  • 42228.26

And also exchange the month 08 for Aug or AUG in any case and change the '/' for '-' in the dates.

For cells formatted "mmm-dd-yyyy hh:mm:ss AM/PM", the output for any of the above inputs is:

  • Aug-12-2015 06:10:03 PM

Note that there does not appear to be a means to force all-capitals for the month.

Note also that if you enter a date/time in the locale format it is automatically recognised and formatted as date/time.

So really your only option is to either change the format of the source data, pre-process the data into locale date/time format, or write a macro to do it within the spreadsheet. It may also work perhaps if you change your locale settings (System Settings, Language & Time), but that is perhaps less than satisfactory because it will affect date presentation for the whole system.

Upvotes: 1

Santiago Settecasi
Santiago Settecasi

Reputation: 28

If you want to avoid VBA, then you can add a column("L") next to your data and enter:

=SUBSTITUTE(K1,"-","/")

Another solution would be to write code to do just that to your existing "K" Column

Upvotes: 0

Related Questions