I have the following date problem, for example I need to reverse the following date:
8/02/2003 (which is recognised as a date) to:
2/08/2003 (the real date).
Is there an easy way to do this using a formula?
I'm trying to use a list of date of births to determine an age output.
However the date of births are all in a simple reverse formate, eg
19790822 would be 22nd August 1979
Ideally, i would take this number and the output would just be the persons age in whole years.
I have attached an Excel spreadsheet and I will apologize now if I lose you. This spreadsheet contains two entries:
"SwapIt" works great but will only reverse the most current date entry and list it first in the cell and the remaining date entries will remain in oldest to latest dates.
"ReverseDates" works great but errors out with <subscript out of range> when a manual date is entered in the notes (see system created format).
System created format; the entries within a cell will always be - MM/DD/YYYY_tt:tt:tt_AM(PM)_username (this varies) followed by the notes entered by user. So in a nutshell, the system will generate all the following but from oldest dates to latest dates toward the bottom of the cell. I would like to achieve the reverse order, latest entries to oldest entries.
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies View RelatedI have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.
How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?
How to correct this formula to get correct output.
=IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),
"January","February","March","April","May","June","July","August","September","October","November","December"),""))
It's working fine if the year is 2014 but not if the year is different.
Conditions:
If the date is today then only the month should display else blank. The year should be ignore.
For example:
08/01/2000: January
01/01/2000: blank cell
08/01/2014: January
01/01/2014: blank cell
The date is in column 'p' in dd mm yyyy format. In short if the date is current date that is for today '8' then only the month should be display in the output.
I have a problem calculating something that happened last month if the month is january. At the moment, if the month is not January it works:
View 3 Replies View RelatedI am trying to write a formula to reverse data in a cell. Basically I am converting a number to hex then I want to take that hex string and reverse it. So it would be something like this my original number is 400001001 my hex number would be 17D787E9 because I am only allowing it to show 8 characters. I want to reverse the 17D787E9 to read 9E787D71.
My question is: How can I reverse that cell? I have searched google and this forum and can't seem to figure it out. I am sure I could do it in VB but I don't know any VB code.
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
Serial No Search E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4
E - Year Month Date
I need F column as Month Date Year Format
I have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.
I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.
So, if the date is on a weekend, I need the Monday date instead.
I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month.
My first column shows who the request is from
The second shows date recieved
The third shows date we send receipt
The fourth shows the date actioned.
I'm sent a file every day where column A contains a month & year but in "general" format.
For example;
Column A, starting with cell A1 with the next date in A2, etc
Jan15
Feb15
Mar15
Apr15
Is there a way through a formula or macro that I can use in order to run through all the dates in this column to make sure that the next cell down is always the month after the cell above it? The dates go all the way through to 2018 in the above format.
I have a spread sheet for 12 months. I am wondering if there is a way to format to show in the cell only day of the month.
View 4 Replies View RelatedI need VBA code to sort the birthdays for all the employees in my company.
See the attached file for example.
I need to sort according to their month and then date and not with their year.
i.e it should not consider Year for sorting.
I have a column with dates populated(examp.Fri, 15 Feb 08)in it. In the next column I need to return the month and year (month,year format) this specific date falls in?
View 9 Replies View RelatedI need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies View RelatedBasically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?
A1: 27-July-2012
Answer D1:JULY' 2012
Pl note "' " is suffixed after JULY (the month).
i use dates a LOT in my job however if i want to add a month to a date the best i came up with is the following:
BTW all dates are USA standard (m/d/y)
A1 = 12/1/2008
Lets say i want to add 3 months but the day NEEDS to be 1 because of all of my array sumif's are based off of month 12/08, 2/09 etc, and in excel 12/08 actually translates to 12/1/08. So here is the formula I have been using for the last 4 years....
=date(year(a1),month(a1)+3,day(1)
that seems a bit "Clunky" to me anyone have a better idea?
do not tell me "=a1+30" or "31" because that does not work universally.
I use function "=month()" to the date_time column the result is "1/1/1900" or anything other than the expected result. I have attached a portion of hte sheet.
View 3 Replies View RelatedI have Excel sheet with daily sales data for differant year.
Attached is the data for 2009 as an example.
I need to compute Month to date sales in Cell F1 as highlighted.
To accomplish this i have got Public function SumDays.
This works fine but every month i have to update the start cell #, example H239 in
Sumdays(H239,c1)....for Sept i will change it to H272, and so on.
Have a look at Cell O2:P13.
i need to eliminate this so that every month correct variable is placed in SumDays(XXX,C1).
I am working on a file that contains an install date. i'd like to create a new column that will only show the month per install date indicated. Can anybody help me create a macro for this?
View 11 Replies View RelatedI have a spreadsheet where I manage my finances. There is a tab for each month, and each tab lists direct debits, standing orders etc.
I have recently set it up so i have a running total of my bank balance. Which means each payment has a date assigned to it. For example alot of direct debits go out on 1st of each month.
Everytime i copy the tab for the next month i have to go through and update these dates to be + 1 month. Im sure there is an easier way to do this
I need a formual that will get the value that matches a month
In column A there are 52 weeks. I need to return the last non blank value for the month within the weekly period. The month value is found in column B