date & time format

I give up! I've been trying to convert a date, say A2 as Saturday, March 16th, 2024 in a text format, to an actual date in the format of MM/DD/YY? P.S. Better yet, how to convert date in two cells, say B2 and C2 containing the month, i.e. 3 in the example above, and 16 also in the example above?

Hi all! I have a problem with the dates. I have a list of dates that I want to loop through and copy the result to another sheet. The condition is a date range. Here is my code: Sub CommandButton1_Click() Dim lastrow, erow, i As Long Dim mydate, sdate, edate As Date...

Hi, Im trying to plot some data but its been downloaded in the wrong chronological order. So i attempted to filter it and it screwed everything up. After looking into the values, so are dates and others are text. I tried using the format cell function but that didnt work. I also tried using the...

Hi Folks. I'm working with a YMSQL db that stores date/time in unix format. I can use a simple formula to bounce back and forth from one to the other but I'd like to be able to have my sheet update the date for TODAY() and I'd prefer to keep it at the sheet level and not have to rely on...

So I have two worksheets in sheet 1 I am trying to perform a vlookup function that goes as follows XLOOKUP(B55,'Sheet2'!P:P,'Sheet2'!D:D) where the lookup value is 16/02/2023 00:00. I know this value is present in column P of sheet2 and that it has a corresponding value in column d but whenever...

Hi, I have data like below. This is just a sample and the actual data is about 20k rows. The column with the date and time is unhelpful and I'd like 3 new columns (as per my desired result below), ideally formatted as Date, Time and Date and Time respectively. Hoping someone can help...

I have a workbook where a lot of employee data is updated monthly, screened for compliance, and reported. Since it is updated monthly the data obviously changes from month to month. However, I want to build a new sheet that tracks the trends from month to month, that is automated when the report...

Hello, I need to add a column, which takes a date and changes it into 2-digit month number (January -> 01, April -> 04, December -> 12, etc.) It needs not only to be shown/visible as such (like via custom date cell format), but cell contents really need to be these two digits only - stored as...

I am trying to make a time correction due to computer clock running slow. For some reason the time correction in column AE of the table below won't update. It doesnt make any difference if I use ordinary date format or excel time code format as source. I have tried using the value in column AD...

In the post at: https://www.mrexcel.com/board/threads/vba-advancedfiltering-faling-to-work-after-changing-the-format-of-cells.1192601/ I was looking for a way around getting advanced Filter to work for dates stored as texts in the format "dd-mm-yy". According to @Marc L, it is not a good idea to...

I have a cashflow sheet, and i need to enter dates that payments are due. If the date is in the following year, i have to enter the format dd/mm/2022, if the due date is after 31/12/2021. How can i format the date to only look at dates that occur after todays date, so if i enter 12 Jan, the...

Hi Peers, I thought I was pretty good at managing Date formats in Excel but this one has left me stumped. I was wondering if you could provide advice on how to convert this date format: 31/08/2020 11:29:34 To the below format which needs to be stored as Text? 2020-08-31 11:29:34 GMT+0000...

I have this data collection that containing a date (string) which i believe in wrong format. the format as follows: "02-03-2020 03:07 +07:00" -> "dd-mm-yyy h:i GMT" I would like to convert that into format that readable as date & time format, something like "03/02/2020 03:07:00 AM" and my GMT...

I thought I was pretty good at managing Date formats in Excel but this one has left me stumped. The raw data field in the csv pre import is in the USA format and complicated by being AM/PM too. ,"8/30/2021 7:39:07 AM", ,"8/29/2021 11:28:40 PM", All other columns are simple numeric or text How...

I would like to display concatenated dates in the format of DD MMM YYYY. I have obtained 3 dates, last day 1 month ago, first day 6 months ago and first day 12 months ago, all from today's date. Here is the code generating my first screenshot: ``` Dim myDate As Date myDate = Date 'Returns...

On another sheet, I have dates in column A. In the new sheet, I need to first extract the earliest date from column A of that other sheet which is easy. just use the "Min" function for A column. So now the earliest date get picked and feeds into the cell of the new sheet in D1. How do I get the...

I see some custom date codes in my spreadsheet include special characters. I have found some excellent documentation on special date and number formatting on the web, but I have not not been able to find any info on these special characters. I'd like to know what they mean and how/when to use...

Hello, I am trying to fill a column with distinct date values with different formats depending on the sheet (sometimes dd-mm-yyyy, sometimes mm-yyyy). I have made a Sub which fills Dictionary with distinct values in the correct format, and then copies them to the sheet. However, no all of the...

Hello, I am trying to convert a date with a timestamp into a date value. However, although I tried multiple approaches, like using INT(A2), the value is still not recognized as a date value. And the weird part is that some of the original values are working as a date value when I copy/paste to...

Hello, I am trying to convert about 75 000 lines of datetime formate data (YYYY/MM/DD HH:MM:SS) into just dates (YYYY/MM/DD). The only way I have found to do this it to concatenate the YEAR(), MONTH(), DAY() parts of the data in one column into another column. However, with so many rows, this...