date formulas

  1. M

    formula to format date formatted as General

    I am looking for help on formatting dates in excel. The system that spits out my data puts the date out in 'general' format. E.g. for January 2023 it will say 202311 (meaning 2023/01/01) for December 2023 it will say 2023121 (meaning 2023/12/01) Ideally I am looking for dd/mm/yyyy, but will...
  2. M

    Formulas and Conditional Formatting Using Dates

    Help! I really hope you can respond to this and help me! I need a formula(s) to apply conditional formatting based on various dates in a column both on the same tab and from a previous tab in the same workbook. Column A is "Date Received" (where the date will vary by when something was...
  3. MrDB4Excel

    Annual Forecast History

    I am having great difficulty with a leap year with respect to the formulas you see in the attached Xl2bb examples. In the sheet ForecastHistory2023 you will see all the formulas are working correct, but in the sheet ForecastHistory2024 the initial formulas in C5 & C6 and probably consequently...
  4. S

    Conditional Formating Required for changing Dates based on 2 different cell dates

    Hi, I creating a permit register. End Goal : Have the text in both Cells in "Start Date" and "Finish Date" have the same colour to determine if the Permit is 'Expired', 'Current' or set in the 'future' Column H (Permit Start Date) and Column I (Permit Finish Date). Permit dates do not have...
  5. S

    What is the Substitute for COUNTIFS date based formula ?

    So I have this formula in excel in my raw data =COUNTIFS([Date range],">="&(EOMONTH(Date,-1)+1),[Date range],"<="&EOMONTH(Date,0)) Power BI however doesn't have this function. I tried to use Calculate, but it is still confusing for me to understand some of these formulas. Can you please show me...
  6. S

    Rounding a date to the nearest half year

    Hi, I'm looking to create a formula that will round a date to the nearest half year (either the start of the year or the middle of the year depending upon which is next) i.e if the date was 01/08/2019 the formula would round to 01/01/2020 if the date was 03/04/3021 the formula would round to...
  7. C

    Highlight date

    I have a holiday tracker calendar and it uses macros and formulas to calculate days and dates etc. I have this code...
  8. C

    Generating Month from Date for Fiscal Calendar

    Greetings, <tbody> My data is in the format MM/DD/YYYY and I want to generate the fiscal month and number. Example: Cell contains 7/5/2017 and formula generates 1-Jul. I have tried using the Month and Choose functions. I can get the output to yield the correct month number, but not the...
  9. S

    Direct Debit Fee Completion Date - Month Date Formula relevant to the DD Date i.e. 28th Month

    I would like to return the finish date of a Direct Debit for Customers, when the Direct Debit due date varies within a month. No problems for weekly or fortnightly. However if it will take 48.6 payments to finish the "loan" I would like the finish date to be the 28th (or 15th) of the relevant...
  10. L

    Formula to determine status based on dates in 2 other cells

    Hello - I'm new to Mr. Excel - please forgive me if I do not yet have the forum etiquette down. Let me explain what I do currently have correct.... I have 2 columns (Column N has a "projected meeting date", Column O has an "actual meeting date"). I also have one cell (N12 with the "TODAY"...
  11. L

    TimeSheet date forumla questions

    Thank you in advance to anyone willing to help a beginner out. I have read multiple posts but I'm still stumped. Here it goes: I would like to have three active columns: "Column B" a date in 1st, 2nd, 3rd, etc. format. (Cells B10 through B25) "Column C" a day of the week in Mon., Tues., Wed...
  12. M

    Need help with a formula modifier

    I have a section in this spread sheet that is working really well but now I need to modify it to fit a new requirerment. The first cell returns the number of personnel The second cell returns the number of expired certificates The third cell returns the total number of valid certificates The...
  13. M

    Cash Flow Manager application....Need smart date formulas...Please Help...

    Hi All - Thanks for taking time to view this. I am writing a cash flow manager application based in Excel 2010. My client enters a date in to Cell A1 and I want to be able to forecast cash flows from that date based on historic banking data that I have normalised. The transactions on the...
  14. N

    Using COUNTIF formula

    I have an excel spreadsheet where I need to count the number of occurances where the date is over 3 days from current date. Is this possible? Or, do I have to put a < "current date" , and have to continually change it when I remove/add data? Hope this makes sense! My first time posting on a...
  15. J

    Date formula help please!

    Hi, I wondered if somebody could help me please? I am looking for a formula which returns the date each Monday. So, each Monday the date would change but then stay the same until the following Monday. I have asked before for the same regarding Friday's but I am unable to alter it so it...
  16. M

    Date Functions

    I am using the following formula to calculate workdays over due. G6 has the issue identification date H6 has the formula =workday(g6,5) which is the target date I6 has the issue closed date J6 has the formula =IF(ISBLANK(I6),NETWORKDAYS(H6,TODAY()),NETWORKDAYS(H6,I6)-1) Since I am...
  17. I

    Date functions using if

    I have a couple of questions. I'm trying to create a formula so that I can use a reference cell to adjust another cell. for instance. Say I have three dates 5/10/03, 4/28/05 and 9/8/08. I want other cell to return the dates 5/10/10, 4/28/10 and 9/8/10 where the year is constantly 2010. is...
  18. B

    Convert DD-MM-YYYY hh:mm:ss to DD-MM-YYYY

    Hi, I used to know how to do this but can't for the life of me remember or find out how you do it. Basically i need to Convert DD-MM-YYYY hh:mm:ss to DD-MM-YYYY. I've done the obvious format cells change but this retains the time, not visually but if you were to click on the cell it shows...
  19. K

    How to display list of dates for the year excluding Sundays

    Hello, I would like to use a formula to take the starting date in cell A1 and then have Excel give me a list of all the dates for 2009 excluding Sundays. So A1 would be 1/01/09 Then Excel would list Thursday, Jan 01 Friday, Jan 02 Saturday, Jan 03 Monday, Jan 05 Tuesday, Jan 06 etc.... I've...
  20. D

    If/Then formula using text and dates

    I have three cells I want to use, for this purpose I'll go with A1, B1, and C1. If A1 has the word (SECRET), then I want C1 to add ten years to the date in B1 and if the word in A1 says TOP SECRET, then I want C1 to add five years to the date in B1. If there is nothing in A1, then I want C1 to...

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