formatted

  1. M

    How to get Dim as Range to ignore #DIV/0!

    Hi, I have the code below that works so long as there are no #DIV/0! errors in the column. What do I need to add to this code to have it ignore those errors? Thanks! Dim ramcel As Range For Each ramcel In Range("E:E") If ramcel.Value < 0.95 Then ramcel.Font.Color = vbRed...
  2. P

    Excel Weekday Function Issue

    I have an issue (or lack of understanding) of the Weekday function (Excel O365) Formula in Column B is =weekday(Col A,2) cell in column B formatted as general Formula in Column C is =weekday(Col A,2) cell in Column C is formatted as ddd Column A Column B Column C...
  3. T

    Simple Question, How to create a default Worksheet

    I've done this before, but struggling with the details of how. I have created a default worksheet that opens when Excel starts stored in my XLSTART directory. I need to create a default worksheet so that when I insert a sheet, the new sheet is formatted as I intended. I have tried saving as a...
  4. M

    Change how date is converted.

    Hi everyone, first time posting, but I can't seem to find the solution. When I encode 11/3/19 in a cell, when formatted it would show March 11, 2019. I would like it show as November 3, 2019 instead. Is there away to change this. Thank you very much.
  5. S

    formatting a date

    Good Morning, I have four cells with time/date info and Time zone adjustment as follows: A1: Date formatted dd-mmm-yy A2: Time formatted hh:mm (24hr) (local time) A3: + or - symbol to denote zone description A4: whole number (i.e. 5) to denote hours from UTC or GMT. So it would appear...
  6. sharky12345

    Scroll to last cell with date

    I have a series of dates in column E and I need to scroll to the last cell which has today as the current date. If it makes a difference, the cells are formatted as "dd/mm/yyyy" Anyone?
  7. M

    Difference Between Two Dates/Times

    I am trying to figure out a way of determining how many hours and minutes have transpired between two Date/Times Cell C3 has the "Start Date/Time" and Cell D3 has the "End Date/Time" I have both of these cells formatted to display the as follows: 9/16/2019 7:30:00 AM In E3 (formatted as "Time"...
  8. O

    .contect.find not keeping the format

    Hi all, I have created the below loop and find and replaces instances in word. I have created an excel spread sheet and some of the data that's being replaced is currently formatted as currency in excel. The find and replace feature works perfectly and replaces everything correctly. The only...
  9. J

    How to split units from numbers stored in a cell that is custom formatted

    Hi All! I am new to this forum. Need help. I need to separate units from quantities. i am not being able to do it because cells that are custom formatted and no text function works on these. When converted to text format, the units automatically disappears! Can you please help me out on this.
  10. D

    Show 40 hours worked

    I'm summing total hours worked in cell J5, formatted as [h]:mm, to =sum(c5:I5) and getting 48:44. In cell K5. also formatted as [h]:mm, using formula =IF(J5>=40,40,J5) , I'm seeing 48:44 when I expect to see 40:00. In the next column, L5, formatted as [h]:mm using formula =(J5-K5), I would...
  11. K

    VBA to send email of sheet within the new email

    Hi folks, I didn't want to hijack another thread so I'll start a new one here. When looking at this thread (https://www.mrexcel.com/forum/excel-questions/1102928-modifying-code-send-e-mail-3.html) I like the way the code creates a formatted email grabbing a range from a worksheet. In my case...
  12. H

    VBA/formula to show w/c date, month, quarter and year across all columns

    So I 've got a Gantt with lots of narrow little columns each of which have the date in UK short date format on that row (row 22) so that just the date shows eg '6' for the 6th of March 2019 say Row above that one (21) references the date in row 22 but is formatted to appear as "W/c Mon 05" Row...
  13. M

    VBA Code to force cell value into time format

    Hello can any help please, I have a VBA Code that sends an email automatically from a spread sheet. With the code there is a section that allows me to write some word that will appear with the email. Also attached to the wording is “& range (AA6 ) “that takes a formatted time (10:18) and...
  14. V

    Numbers not formatted as number in data source

    Hey all. I have a controlled data source generated several times a day from one of our customers. I used the data to verify some of record and update forecasts, etc. The problem is, of the 100,000 plus cells, ~20,000 cells spread throughout the spreadsheet are not formatted correctly. Double...
  15. F

    Data integrity - formatting cells

    Excel 2010 In Excel, I have 3 columns: Total price Sub total Tax All the row calculations were correct but were not adding up in the column. I formatted the cells to make sure they were in currency format and still did not get the right sum. I copied the numbers to a blank worksheet and got...
  16. C

    IF function returns NA when FALSE?

    Hi, How do I get the first IF function to return FALSE instead of NA? What is wrong in the formula. =IFERROR(IF(A21=INDEX(P:P;MATCH(A21;P:P;0));TEXT(INDEX(S:S;MATCH(A21;P:P;0));0);TEXT((INDEX(R:R;MATCH(A21;T:T;0)));0));"") All fields are formatted identically.
  17. A

    Conditional formatting with formulas

    Hi Can anyone help with the following problem , the black cells in rows 47-52 appear according to the number in row 46,these are formulas that count the number of 5's in the column above, I would like these cell to be colour formatted eg the first 5 in j16:j36 to appear but be colour formatted...
  18. C

    SUMPRODUCT + MID multiple sheets - not working?

    Hi, Trying to sum multiple columns from another sheet based on one criteria, which is determined by the "MID" function. What is wrong with the formula? 1) B2 (formatted as text)=9132456, 6753456 2) 6753456 appears in D:D (formatted as text as well). It has 100$ in col. P and -10$ in Q. Should...
  19. E

    Why IRR returns #NUM! ?

    Hello I select a range of cells for the IRR formula and it returns #NUM ! I formatted the range to be Numbers but nothing changed. Any idea?
  20. M

    Query Access table via VBA and retain cell formats

    I am running a query via VBA to populate a number of cells with the results of the query. I have the cells formatted to how I need them but upon the cells being populated, their formats are being changed to date formats. The fields in the Access table are set up with the correct formats and I...

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
Back
Top