date & time

  1. N

    Formula for Now() that doesn't update dynmically

    Hello, I have the below formula which work to determine the date a case was closed, but the issue is when using Now(), is always updates to the current date. =IF(OR([@Status]="Closed: Call complete",[@Status]="Closed: No Call"), NOW(),"") For instance someone changed the case status to closed...
  2. 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...
  3. D

    Conditional formatting expiration date

    Hello everybody, I want to create a conditional formatting for the following column in my stock portfolio: I would like 2 conditional formatting (i have two of these sheets, which are identical, so one for each): 1) I would like the first formula to highlight the cells if the date entered...
  4. C

    Automatic date calculation

    Hello, I have a table that I want some people to fill in. The last column is called "Submission date" and I want this to be automatically calculate. Trigger: When they (start to)complete a row in the table Rule for calculation: If that persons input is before the established cutover date -...
  5. P

    Excel formula to calculate elapse days/hours/mins between two dates

    Hi! can you please help me on how to get the elapse days/hours/mins between two dates/time, excluding holidays and Saturday from 4:01am until Monday 04:59pm ? Work shift: starts : 5:00 pm ends : 04:00 am Thanks in advance :)
  6. D

    How to get a corresponding value based on the most recent date and name?

    Hi, I'm making a stock investment tracker where all you have to do is enter in the transaction and it gives you all the your positions, whether they are open or closed etc. I was finally able to figure out a way to find my average cost per share after buying/selling/rebuying. I have a column...
  7. E

    Separating Time and Date in Cells.

    I’m dealing with the timestamps from Google form, which come in the following format.
  8. DarbyBrown

    ADD A LAST UPDATE CELL TO A SPREAD SHEET

    I am having trouble adding a ‘Last Update’ cell to my worksheet. The things I have tried are: First, Used the VS editor and created a module, (Alt-F11): Function ModDate() ModDate = Format(FileDateTime(“MyFileName.xlsm”)) ‘With and without the quote marks’ End Function Then called the...
  9. J

    Rows insert for the period of given dates

    Hello Excel Warriors I am working and trying but failed to achieve the goal. I want to create an VBA program to create Excel sheet to calculate arrear of pay. I just want to create list of months automatically between start and end dates in which if the starting date or the end date have only...
  10. S

    Conditional sorting columns based on time

    Hi, I've worked all weekend to learn VBA. I came close, but code is probably what a proper coder would call (very) ugly. I would really appreciate tips on how to make a more streamlined code in addition to the key question: A rather big set of data are collected on sheet 1 ("Disorderly"). Column...
  11. lockarde

    Change cell value upon save

    Good morning all, I have a workbook that tracks current in progress and completed jobs for the month. I'd like two cells, C2 & D2, to update upon every save with date (C2) and time (D2), so everyone that has access can easily see when the workbook was updated. Any help is greatly appreciated!
  12. J

    Exclude time difference from a date range

    I'm trying to exclude duration of a date range from a set of range that is in another sheet: Table_1 shows total time in seconds a ticket was assigned to someone Table_2 shows the time in seconds when the ticket was put on hold Table_1 ID Start_Date End_Date...
  13. E

    Conditional formatting on date ranges

    Hi there, I am currently trying to create a dynamic calendar in excel. A disclaimer upfront: my username checks out. This is literally my second day working with excel formulas. I have done some VBA in Word before but that's it. So my idea is to mark certain cells in my calendar in color...
  14. A

    Dates show DMY in cell, but MDY in formula bar - won't sort or convert

    Hello - I'm using O365 in the US but have system time on my PC set for Australia (in case this matters…) WorkSheet #1 I have received two excel worksheets, one has dates formatted in DMY, and if I switch the formatting of any of those cells to General, I see that the resulting number (in...
  15. smide

    Convert date to weekday name

    Hello. In column A (A1:A600) I have dates and time in format dd.mm.yyyy - hh:mm (ex. 04.10.2019 - 20:00 , there is one space between dd.mm.yyyy and '-' and one space between '-' and hh:mm). All those cells in column A are text cells. I need formula to convert those "dates" into weekday name...
  16. smide

    Copy nearest index sorted by date

    Hello. In cell B1 I have current date created with formula =today() eg. 26-Sep-2019. In the same column starting from cell B3 (B3:B600) I have dates and time in format dd.mm.yyyy - hh:mm (example. 20.09.2019 - 21:00). All those cells (B3:B600) are text cells. In column A there are...
  17. R

    Finding Last Test Date for a Student

    I have a large data table that was not very well put together. I need to find the last test date a student took a specific test in a range that contains also contains dates for the other tests they've taken. My table is as such: ID | LName | Test Date | Math Test | Score | Eng Test | Score |...
  18. A

    Excel Date Time Stamp VBA

    I have an excel workbook with a worksheet that has 78 columns of data (a lot I know) and was looking for a way to add a date/timestamp to each field so that I can filter and find when a field was changed in this large data set. I found the below code here, and it works really well however, it...
  19. M

    Combine Multiple Date & Time groups into one Cell

    I am trying to merge 4 date and time cells together into 1 cell. I am using the following formula: =CONCATENATE(TEXT(Table_query__42[@[Soft Lock 1 End]],"mm/dd/yyyy h:mm AM/PM"), "; ",TEXT(Table_query__42[@[Soft Lock 1 Start]], "mm/dd/yyy h:mm AM/PM"), "; ",TEXT(Table_query__42[@[Soft Lock 2...
  20. charlesstricklin

    Calculate date given current number of days in advance

    I'm not able to calculate the date (accurately) I should run out of an item using the formula: <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #003ecc}span.s2 {color: #005109}</style>=TODAY()+N2 where the values in...

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