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 ...
- 775
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 ...
- 481
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)
- 21.5k
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 ...
- 155k
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 ...
- 19.3k
14
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 ...
- 398
13
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" (...
- 21.5k
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,480
11
votes
Accepted
No solution for Thunderbird date format
Edit: There is now official documentation (archived) on this. Thanks to @zx485 for pointing this out
To add to the answer explaining how Thunderbird uses the CLDR, there recently has been some change. ...
- 126
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,614
8
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.
...
- 443
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 ...
- 19.3k
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: ...
- 23k
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
- 27.7k
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,374
6
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 ...
- 23.6k
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 ...
- 27k
6
votes
Firefox date format
So, yet again trying to fix a minor pet peeve of mine led me down a deep rabbit hole of unanswered Stack Overflow questions, ancient bug reports and developer documentation, and finally here it is:
To ...
- 71
5
votes
Timezone conversion by command line
I know it is an old thread, but I needed a code for the same use case and, based on the ideas here, developed this little bash script:
#!/bin/bash
# ig20180122 - displays meeting options in other ...
- 151
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.
...
- 301
5
votes
Accepted
Linux tar files within specific date range using filename date
find is probably the best bet if you want to run it yourself:
find . -name '*Report.csv' -mtime +8 | xargs tar -czvf oldfile.tar.gz
There is also a pretty standard tool for clearing up old files ...
- 59.6k
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)...
- 19.3k
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 ...
- 61.6k
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 ...
- 59.9k
5
votes
How could I change file creation/modification date from filename?
Advanced Renamer can change the file date attributes based on a pattern in the filename.
- 2,366
5
votes
IF condition not working in scheduled batch script
The IF command matches all characters, including the quotes - Thu
does not match "Thu".
So either of the following will work:
if %RDAY%==Thu ...
if "%RDAY%=="Thu" ...
EDIT - I just realized ...
- 11.3k
5
votes
Using 'date' in bash shell to add 5 minutes to a defined time with high precision
Seemingly in conflict with other use I've been able to find of date -d
It turns out the way to add minutes that works for me is:
TIME="23:01:05.001"
echo $TIME
date -d "$TIME 5min" +"%H:%M:%S.%3N"
...
- 625
4
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" ...
- 41
4
votes
Accepted
Batch script (cmd) resulting in DD-MM-YYYY_weekday format
I would like to have it formatted like DD-MM-YYYY_weekday
Below are two solutions:
Batch file
PowerShell (a simple one line solution)
The Batch file solution
First, what not to do:
Using %date% ...
- 155k
Only top scored, non community-wiki answers of a minimum length are eligible