Hot answers tagged

96 votes
Accepted

Unable to get Excel to recognise date in column

The problem: Excel does not want to recognize dates as dates, even though through "Format cells - Number - Custom" you are explicitly trying to tell it these are dates by "mm/dd/yyyy". As you know; ...
  • 1,856
34 votes
Accepted

Excel week number inconsistent results

There is nothing wrong with your formula, but maybe it is not the formula you want to use. There is another Excel formula: =WEEKNUM(serial_num, [return_type]) That will return the results that you ...
  • 777
24 votes
Accepted

Linux date command not working

It turns out I had to change time/date settings from automatic to manual in System Settings. As I am doing everything through ssh I could not do this in the gui settings, but the equivalent command on ...
17 votes

Unable to get Excel to recognise date in column

Select all the column and go to Locate and Replace and just replace "/" with /.
17 votes
Accepted

Pull a user-defined season from a date?

You can easily do it with VLOOKUP, just insert a new column with column numbers, then use this formula: =VLOOKUP(MONTH(E2),A:C,3,FALSE)
17 votes

Excel week number inconsistent results

The function appears to break down in 2018 when it shows the week number for 31/12/2018 as week 1 It is correct, as the 31st December 2018 is a Monday. If 31 December is on a Monday, Tuesday or ...
  • 145k
16 votes

Calculating the number of days between two dates in Excel

It is not a question of one formula being right and the other being wrong. It is a question of what you are looking for. Say you are working on a task 24 hours a day. You begin the task 17 January ...
13 votes

Calculating the number of days between two dates in Excel

The answer revolves around how Excel deals with dates. When days between dates are calculated using formulae such as B1-A1, Excel would turn the dates into serial numbers and use the serial numbers ...
  • 1,461
11 votes
Accepted

What other archive formats save Date Created and Date Accessed?

You can also store Access-, Modification- and Creation-Date when using 7-zips ".7z" format. To do so just specify the following parameters when packing data: 7-zip GUI: write "tc ta" into "Parameters:...
  • 316
11 votes

How to use "date" to print tomorrow's day of the week

The problem is not how you can use date to output what you want... your problem is: This way I'd like to get the old logfiles out of my way, but still have 5-6 days of logfiles around. So, why not ...
  • 1,869
11 votes
Accepted

Wrong week number in Outlook

Yes, you can set week numbering in Outlook: go to file - options in the left column select "Calendar" in the "work time" group there is a field "first week of year", here select "First 4-day week" (...
10 votes
Accepted

Is there any way to view dates of changes to Windows registry keys/values?

Registry keys have a last-modified timestamp. You can use Regedit to export a key, selecting the ".txt" output format. That text file will contain the last modified date&time. NirSoft's ...
  • 4,441
9 votes

How to convert UTC to typical date format in Excel?

Let's assume the input time is in cell A1. then you can convert it to Excel date and time value using the following formulae, =DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8)) What this is doing is ...
  • 348
8 votes
Accepted

Get DayName from Date in OpenOffice Calc?

You could simply format the cell to display the day name instead of an integer. In the 'Format Cells' dialogue box (right click and choose 'Format Cells...' to open) enter: NNNN NNN or NN in the '...
  • 330
8 votes
Accepted

How can I place a date in dd/mm/yyyy format into an Excel cell with VBA?

Format before placing a value in the cell: Private Sub CommandButton1_Click() With Worksheets("sheet1").Range("B1") .NumberFormat = "dd/mm/yyyy" .Value = Date End With End ...
7 votes
Accepted

Creating a column of working days in LibreOffice Calc

This may be new since the question, but (LibreOffice 4.2.7.2 on Linux), Edit->Fill->Series. Choose Date as the series type and Weekday as the Time unit. I found that, after the fill, I had to select ...
  • 186
7 votes
Accepted

How can I display a UTC date time value in the user's local time zone in Excel?

Based on @Ramhound's comments and my own research, the answer is that Excel knows nothing about time zones and so this is not possible, although I have not found a definitive statement to that effect. ...
7 votes
Accepted

Convert three letter month abbreviation to number?

You can use MONTH() and create a pseudo date for it: =MONTH(DATEVALUE(A1&" 1, 2017")) Or another variation on the same theme: =--TEXT(DATEVALUE(A1 & "1, 2017"),"m") One more note: ...
  • 21.6k
6 votes
Accepted

How to use "date" to print tomorrow's day of the week

You would do that like this: date -d "+1 days" +%a That renders: Sat
6 votes

Manipulating datestamp string consistently with date format %Y%m%d

To display a formatted date for x-days from today: $ echo $(date +%Y%m%d) 20150922 $ echo $(date -d -10days +%Y%m%d) 20150912 $ echo $(date -d +10days +%Y%m%d) 20151002
  • 27k
6 votes
Accepted

Manipulating datestamp string consistently with date format %Y%m%d

I need to manipulate dates (whilst paying attention to leap years) Fortunately, GNU date has a very handy -d option: -d, --date=STRING display time described by STRING, not 'now' (from ...
  • 7,194
6 votes

Do not refresh date in IF Statement using TODAY()

No. The point of TODAY() is to get the date of the latest recalculation. IF or any other functions won't help either because there's no function that stores the time it was first called and always ...
  • 24k
6 votes
Accepted

No solution for Thunderbird date format

To add to the answer explaining how Thunderbird uses the CLDR, there recently has been some change. Thunderbird¹ now natively offers a set of preferences with which the date formatting patterns can be ...
  • 76
5 votes

Windows 7 - display date using small icons

There is a version of T-Clock that is maintained by White-Tiger on GitHub and it works very well. I tested it my self on Windows 10. According to the description it is also compatible to windows 7. ...
5 votes

Unable to get Excel to recognise date in column

I was dealing with a similar issue with thousands of rows extracted out of a SAP database and, inexplicably, two different date formats in the same date column (most being our standard "YYYY-MM-DD" ...
  • 51
5 votes
Accepted

Formula to set date to nearest previous (most recent) 25th of the month

How about: =IF(DAY(A1)>25,DATE(YEAR(A1),MONTH(A1),25),DATE(YEAR(A1),MONTH(A1)-1,25)) EDIT#1: This will handle the case of the day being the 25th: =IF(DAY(A1)=25,A1,IF(DAY(A1)>25,DATE(YEAR(A1)...
5 votes

Concatenated cells return random number instead of date

Instead of concatenating "J13" (or whichever cell contains your date) in the final formula, concatenate TEXT(J13, "yyyy/m/d"). So your formula would become (spaces added for readability) (...) & ...
  • 271
5 votes

Wrong week number in Outlook

Outlook uses whatever you set in its calendar options. The default setting is sometimes wrong and does not necessarily correlate with your Windows region settings. This screenshot is from Outlook ...
  • 55k
5 votes
Accepted

Taskbar/system tray suddenly changed to 3 rows. How do I get my one row taskbar back in Windows 7?

The system tray does only give more information (on the clock etc) because it has the space to do so. Please understand that the system tray is part of the taskbar and as such will resize to whatever ...
  • 56.4k
5 votes
Accepted

How can I get the date in a locale independent format in a batch file?

It sounds like a bad idea to be parsing a date that could be in various formats which format you don't know. Better to get the date in a specific format, even if that means not pure batch but invoking ...
  • 22.2k

Only top scored, non community-wiki answers of a minimum length are eligible