date & time format

  1. G

    Date format from mm/dd/yyyy hh:mm:ss AM/PM to dd/mm/yyyy

    I cannot find an exact answer to handle the following dates: 3/30/2012 12:00:00 AM 04/02/2012 12:00:00 AM Using Excel 2016 I tried 1- Formatting with custom dates including [$-409]mm/dd/yyyy hh:mm:ss AM/PM;@ 2- Various versions of LEFT(TRIM(RIGHT(SUBSTITUTE...
  2. H

    Date format to dd-mm-yyyy in VBA code

    Hello, Despite the below standing code (Private Sub Process_XLS_Files) copies a date like 2-1-2017 it turns it into 1-2-2017. I have tried to get it fixed by add in an extra piece of code, but that did not help. Both with function of Dateformat as Numberformat. Look forward to your reply...
  3. A

    Date string

    Hi, I'm trying to import a csv file with dates formatted like: Jan 1, 2017 6:01:12 AM PST How do I transform those dates when importing in PowerBI (and powerpivot)?
  4. S

    Excel VBA to find date string, add hour, and convert to mm/dd/yyyy

    Hello, It looks like the server missed an update when the time changed to PDT. In the database some records have a time date/stamp like mm/dd/yyyy 00:00 AM, when I export the record, it comes out mm/dd/yyyy 11:00 PM, which is actually now a day earlier. We drop the time stamp for reports, so...
  5. J

    Date format messes up when importing multiple CSV files using VBA module

    Hi, I have data from multiple CSV files that I want to compile into one sheet. I found a macro online for this: Sub ImportCSVsWithReference()'UpdatebyKutoolsforExcel20151214 Dim xSht As Worksheet Dim xWb As Workbook Dim xStrPath As String Dim xFileDialog As FileDialog Dim...
  6. D

    Having Troubles Formatting Cells to Delete After 365 Days

    I've a column D (which I plan to hide later) either filled with a date or left blank. Then I want to have column E either display the date from Column D if the date is within one year or display nothing if the date is over one year old. The format I have right now in D2 is...
  7. S

    Converting Numbers to Date Format

    So for work, I have an excel spreadsheet that I use to keep track of documents I send out for specific patients I deal with. Some of the columns are dates that are always changing. I'm wondering if anyone knows a way to format the cells so that I don't have to type in the full date? For...
  8. C

    Getting irregular formatted date into usable date

    Hi all, I've been trying to get a string in the format of 31st Aug 17 4:12pm AEST into a usable date format. Things I've tried so far include but are not limited to the DATEVALUE function, =LEFT(O2,2) function, however not sure how to make sure when numbers go from single to double digit it...
  9. W

    Weeks + date calculations in vba

    Hi, I am creating excel database (input via Userform) where i am facing two issues. could someone help me on below: 1. I am entering data through form and it is saving in sheet2 . The data contains numbers, date, text etc. But the numbers are saving in text format which is difficult for excel...
  10. well2ant

    VBA to set a custom date (time zone) format

    Hello all, I'm writing a module to convert date & time provided in UTC format, into a local time zone of my choice. The code works fine in converting to the required time zone, but I want to them place a formatting over the top to indicate which time zone it relates to. I do not want to add...
  11. T

    Using VBA to post a date in YYMMDD format.

    Hi all, I have seen many posts about changing a date from DD/MM/YY format to YYMMDD format; however, there has been a number of solutions and i have tried to implement some but to no avail. I want to paste into a format with no "/"s, just YYMMDD and for example, if it was 12/09/17 it would...
  12. B

    Putting a time limit on a macro.

    Hi Guys, Does anyone know if you stop a macro running if it is within 48hrs of a date specified in range a1(from) a2 (To) Sheet 1. many thanks Jamie
  13. L

    Excel misreading dates

    Hi, I have some data that was imported from HOBO into a .csv file in Excel. It includes a date and time column, but about half of the dates are being read incorrectly in Excel. It is treating the month as year and day as month. For example: 2015-08-01 is being changed in Excel to 2008-01-15. I...
  14. S

    Unexpected value returned with NETWORKDAYS function

    Hello, I am new to this forum and hope someone can help me to understand how the Networkdays result is being calculated in my project. I have created a calendar that uses the date function to store and display the day only value in a pictorial calendar. Here is the start date - April 29...
  15. T

    VBA macro - change time format and select rows with specific month

    Hi guys, I have a big file with columns D & E that contains time of starting an action (D) and finishing the action (E). I need the time in a custom format, like: mmmm dd, yyyy hh:mm:ss AM/PM Records are from the last 2 years, but i'm interested in a specific month, let's say, January 2017 and...
  16. W

    Covert Years-Months ago date format to no of days/minutes

    Hi, Can anybody help me in converting this excel logic to macro or formula: Requirement- Convert Months/Years/Days/Hours ago time to Minutes., and give ranking to it. I Have excel file with colmn Time and Download count in column D and E, i want to find out average download time per minute...
  17. A

    Date of birth in "Dutch" format ddmmyyyy in userform and

    As a newbe, I tried to receive the date of birth from a form in a local format: ddmmyyyy. somewhere I messed up, but couldn't find a correct solution. Can anybody tell me where I'm wrong and how to correct? Here's my code sofar: Private Sub dtpGeboorteDatum_Exit(ByVal cancel As...
  18. V

    SUMIFS not working to search column for any value that contains specific date

    Hi there, I am in need of help with a SUMIFS function I'm trying to use. I am trying to sum the total quantity of products sold in a column if it matches the SKU and date in the corresponding column. In this first image you can see the function: Sheet 1: https://postimg.org/image/xi73c6fij/...
  19. pleeseemailme

    Power Query, Date Formatting Error, Causing Slicers to be Deleted

    Howdy, I am using Power Query to merge tables from several excel files into a master table. In these source tables there are two date columns A and B. Date column A contains only dates. Column B contains blanks, dates and sometimes text - always the text "Enter Date" When I import the...
  20. B

    VBA - How best to handle dates so format is changed/lost?

    Hi guys, I'm working on a "membership" database for a greyhound rescue volunteer group, to try and help them organise things a bit better (their "manual" system wasn't too flash). I've got things working (for the most part) how I want them to... just fixing minor issues as they come up, now...

Watch MrExcel Video

This Week's Hot Topics

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