date & time format

  1. E

    How to add seconds to a date and time?

    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...
  2. kelly mort

    VBA code to list the individual dates between two dates

    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...
  3. R

    Date format

    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...
  4. VBA learner ITG

    Formula Advice to convert UK date to a set format

    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...
  5. T

    Convert Date & Time to correct format

    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...
  6. V

    USA Date Time AM & PM convert to UK Time 24hr

    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...
  7. T

    Excel VBA - Display Concatenated Dates as DD MMM YYYY

    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...
  8. D

    get dates from a range in ascending order

    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...
  9. N

    Special characters in custom date formats

    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...
  10. A

    Filling column with Dictionary Keys

    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...
  11. P

    DATEVALUE Error

    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...
  12. A

    Speed up code: convert datetime to just date

    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...
  13. A

    Help converting a date with a timestamp in excel

    Hello! In my office we use slack for the majority of our communication needs. With Slack you can build workflows that allow users to enter data which can then be imported into an excel sheet. Slack date/timestamps each submission in the excel file in a format which looks like this...
  14. R

    VBA code for recent file search and open with variable date and time in file name

    Dear all, I am looking for a vba code which can help me find a recent file uploaded in a path for example "D:\Test\" and copy the data from the file to current workbook open. The file name contains both the date and time of upload in its name. eg- "20210126_11_03_42_Test_2" and in the file name...
  15. O

    How to automatically run VBA code between specific hours for each week in MS Access application

    I have a MS Access application that is used in multiuser environment. I want to make maintanence every thursday between 15:00 and 16:00. At this point I have two issues to consider. Firstly, The users who may entered to application before 15:00. Secondly, the users who will be entering between...
  16. B

    Text To Columns vba producing different results on differents pc

    Hi all, I have a problem, I set up a macro to format a column (fixed-width) with a date format DMY. It works fine on my PC but on a colleague's pc, it isn't working. Here the code Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _...
  17. R

    Calculate a price for a item which was rented on different day's

    I Have a item " spoon " Price is €0.07 per day One customer who has ordered 50 spoons on 1.11.20 ( and still has not returned them). On the 7.11.20 he orders another 40 spoons. On the 14.11.20 another 20. On the 21.11.20 he returned 30 spoons ( I would charge him 21 days at € 0.07) On the...
  18. E

    Date subtraction with result in seconds and modular division problem

    I am trying to subtract a load of dates from an earlier date to end up with seconds which I can then do modular division on with the number of seconds in a sidereal day. This will give me time separation that I can sort to find similar location of points in the celestial sphere. I simply...
  19. H

    DATEDIF result in years and months - duplicate

    I need to create a mark book. =VLOOKUP(G3,'Yr5 Maths Aut'!$A$1:$U$112,MATCH(I3,'Yr5 Maths Aut'!$A$1:$U$1,0),0) The Vlookup table uses the following Ages 9 yrs 1 month, 9 yrs 2 months etc to 9 yrs 10 months. Total 9.00 9.10 9.20 ….. 9.90 9.10 9.11 10.00 10.1 The problem is...
  20. A

    Displaying Text

    Hi everyone, When I use the formula: ="The date is" & TEXT(M30, "dddd, mmmm yyyy") I encounter with this: The date is00, mmmm 2020 Although the M30 cell is in the date format, the formula did not work the way I wanted it to. I could not understand what happened? Any advice or suggestions very...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top