I have two tabs in a workbook named "Dashboard and Medicine details"
I need my dashboard to show what are the medicines will expiry in next month.
My Medicine details tab contails medicine details along with its expiry date. If the expiry date of a medicine falls in next month, then it should be displayed in my dashboard.
See the sample attached will give you a clear objective.
Tablet sample.xlsx
I have the following (just a sample) "dashboard" ....
View 9 Replies View RelatedI am trying to add some functionality to a spreadsheet that I manage. Basically i have a small team, and each week I ask them to update a spreadsheet to show what they have been working on in that week. In order to monitor who is going in and updating their entry I would like to add a column that shows me when the last update was for each team member. I have seen this example but it only returns a date based on one cell as opposed to the whole row (or range of cells)
Auto populate the date and time a cell was updated. I have attached an example.
I have cells within a column that could contain both text and dates. When a date is entered - the activity is complete. I want the cell color to automatically turn green when a date is entered. Conditional formatting seems to use ranges, or today's date, or > than and < than... and I want the cell the turn green when any date is entered. I haven't been able to find either vba code or an excel formula that will work.
View 3 Replies View RelatedI 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.
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.
Every week, in row 3 i enter the number of boards cut. In row 9 i want a running total, but until this weeks total is entered, i dont want anything to show. This is the data for a bar chart which i print off every week. Next week is week 3 and i will enter the data in cell d3. When i do i want cell d9 to show the new total of B3,C3,D3. And so on each week.
View 6 Replies View Relatedhow to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:
If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008
Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.
I have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
I have 4 columns
Column A is "Customer Number"
Column B is "Date Can Opened"
Column C is "Month" (currently blank except header row)
Column D is "Year" (currently blank except header row)
I would like a macro to do 2 things here....
1. Extract the Month part of the "Date Can Opened" column and put it in column C.
2. Extract the Year part of the "Date Can Opened" column and put it in column D.
Currently, the "Date Can Opened" column is formatted as a date like */14/01 taken from the Number tab in the Format Cells dialog box) ...
I've been using the following code to go the current date on a spreadsheet. I now have to add the current date somewhere else on the sheet How do I modify the following code to only have it look in A:A ? Date is formatted -- m/d/yyyy (1/2/2013)
HTML Code:
Sub gotodateday()
Dim C As Range
Set C = Cells.Find(Date)
If Not C Is Nothing Then C.Select
End Sub
If I name ranges as Months, (Example: January_13. -- I can use the month without the year if it makes a difference),. How would I change it to look for the current month in A:A.
Need a formula for counting the number of occurences of a month & year in a date column? The spreadsheet is looking at items raised in any given month e.g. all items raised in Dec-08.
View 3 Replies View RelatedI have a front page with buttons linking to other spreadsheets, e.g
{ button } Stock Sheet .xls (???)
{ button } Sales Sheet .xls (???)
In the brackets I wanted to display the last updated date of the said spreadsheets, so that a user can see if it has already been updated. Is there a way of retrieving the last updated date for a spreadsheet, and dispaying it (i.e is it held against a variable or named item)
Sheet1 AB120091002Valid220090702Valid320100702Not Valid Excel tables to the web >> Excel Jeanie HTML 4
In this A column Shows Year month Date format I need VBA Coding.
If A column is Grater than Today date Means B Column States Not Valid. Else Valid.
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
View 2 Replies View RelatedI have a list of dates in column A which are in order but non consecutive. I am creating a new table with all consecutive dates. I need a formula to check whether a date is in column A and then give out a number in the corresponding column to the right. I am able to use Vlookup for this but if the date is not present in the first coumn it returns an N/A response. Is there a way of returning zero if the date is not present?
View 5 Replies View RelatedI would like to do in the column next to it is where there is a date add 45 days, and where the cell is blank just leave the cell next to it blank.
I've have tried several methods, as below and I either get an error message or the cell doesn't return what I would expect.
=IF(C3:C13>"",45,0)
=IF(ISNUMBER(C3:C13)+45
=IF(C3:C13,0,45)
I want to be able to show only the dates whose months are selected in the drop down in cell A1. I've been able to do this with a Macro that I must run every time I change A1; however, I was wondering if there was a way to have the macro done automatically. I've been trying the Worksheet_Change options with little command. In addition to all of the If/Thens, I've also tried the If/ElseIfs as well:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target = "January" Then
ActiveSheet.Range("$A$2:$C$14").AutoFilter Field:=1, Criteria1:= _
xlFilterAllDatesInPeriodJanuary, Operator:=xlFilterDynamic
[Code] .....
I have an excel report that is generated in another program every week. The report is not sorted by date and it may have 100 rows of data one week and 200 rows of data another week. My excel file has formulas that refer to this original report. I need those formulas to update first, then I need the information to automatically sort by date (column D in my excel file). I have tried to setup a script to run, but it's not working. I will attach the file and the script.
Attached File: Sample w VBA.xls‎
I want Cell B1 to display the MM/DD/YYYY of when Cell A1 was updated.
so when I update A1, B1 is automatically updated with today's date.
Is this possible?
Previous to a PC meltdown a few months ago, when I opened my monthly customer list file the account date would automatically update to the current date. After the PC repair, reinstall of MS Office 2002 (same version as before the PC death) and copying of documents back to their old locations, I now have to update this manually for all accounts each time I open it. I have searched through the options and customize menus but there is no mention of this setting. Before, it just did it from day one (5 years ago) without any setup.
How do I get back the old auto-date update preference?
I have a spreadsheet which is linked to several password protected spreadsheets. I have a macro which updates the links but I would like the macro to include the date that the links were last updated. Is there a code I can insert into the macro which will display the date the links were last updated?
View 4 Replies View RelatedI want to group data (rows) using month and year in date column using macro.
For example.
Date ID Amount
01/01/2013 12345 $100
02/01/2013 13452 $73
03/01/2013 12232 $50
04/01/2013 34232 $125
01/02/2013 12322 $67
02/02/2013 12345 $100
03/02/2013 13452 $73
04/02/2013 12232 $50
05/02/2013 34232 $125
01/03/2013 12345 $100
02/03/2013 13452 $73
03/03/2013 12232 $50
04/03/2013 34232 $125
I want to group rows by mm/yyyy in date column. Also I want sum amount column by month.
I am trying to record the date with time on a pre set cell of a row, in which the cell will show the most current updated date/time once any change is made within the same row, regardless which cell on the row. Is there any code for VBA you can suggest?
View 14 Replies View RelatedI have done a VBA course but am struggling to apply it to my situation. What I want to do is on one spreadsheet, I have a column with "shift, date, grade 1, grade 3, quarantine" in it, and in the cells next to it I want an operator to enter either "day" or "night" next to shift, and the date. Next to the others I want the total weight of the appropriate product made in that time. The spreadsheet I will be feeding off is quite big with a lot of information in it. Down the left hand side is the roll number (we make rollstock). Further along on that same row will be the date, the weight, and a grading (putting it into grade 1, 3, quarantine etc). Our shifts run from 7am to 6:59pm, and 7pm to 6:59am. Which shift the roll comes under is determined by it's end time (also in that row). So basically, I need VBA to say sum up all of column [weights] if column [date] = [date cell in working spreadsheet] and if working spreadsheet says day then column [roll end time] =7:00-18:59 and if working spreadsheet says night then column [roll end time] =19:00-6:59 and also if column [grade] = [grade cell in working spreadsheet] ....
Oh, also on the feed spreadsheet i have a column that i put in a little code date, "AP04" for 4th of april kind of thing, rolls being made before 6:59am on the 5th of april being put into the 5th april as they need to be counted under 4th april as they are 4th april night shift (even though the date will stay 5th april)...
I have created a spreadsheet and saved it as a template. when the staff open it to enter information they use save as and give it a name. They only need open and enter information once. I have put into this spreadsheet in cell A1 a formula to enter the days date. This all works fine. A few days later I need to access the spreadsheet to check the figures entered and obviously the date changes to the date when I open it up. Is there any way of stopping this happening without using macros. i.e. keep the date as the day they entered the information.
View 9 Replies View RelatedI am wanting to calculate total time in days, months and years from a date to present. What is the best formula and how do I format the cell to display, days, months and years from a date format like dd/mmm/yyyy?
View 9 Replies View Relatedi am trying to find the time difference between two cells and present the date in a third cell. The data in the cells are in a non standard date/time and i need to create a special format i think. The cells look like this.
fldcollected fldaccepted Type Time between being received by database and eccepted
2013-11-06 15:59:29.1002013-11-07 08:41:12.000PSTN
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 am looking for a way to ensure that all the leading zeros in one column are present without having to format the column cell be cell. Is this possible?
