Im trying to input data where im updating customers and friends birthdays. I wanted to see if i could input a formula and conditional format or a pop up to remind me a week before someones birthday in the future.
e.g
NameD.O.BReminder Johanne Stevens01/04/1978Mike Chandler15/05/1988Michael Owen28/02/1972
I currently use the following formula in Conditional Formatting to highlight a persons birthday:
=$R1=DATE(YEAR($R1),MONTH(TODAY()),DAY(TODAY()))
which changes the colour of the cell on their birthday. However, I need to amend this formula so that the highlighting applies to the current week, as in Sunday thru to Saturday. My amendments have not been successful and you cannot use the WEEKNUM function in conditional formatting for some reason!
I often make templates of forms for weekly, bi-weekly, monthly, etc., forms for others to use and I tell them to be sure to use Save As... with a new name in order to keep the original form intact. We do not have a common location for templates here. I have tried a Workbook_open() macro to give them a reminder message, but the macro gets saved with the new file, which asks them again to use Save As..., which they shouldn't do.
Is there a way to delete the macro from the new file? Or some other method to acheive the goal?
We have spreadsheets which record our sales team's prospects and orders. In order for the sales director's summary spreadsheet to be correct, the month which the order came in has to be filled in and correct.
What I would like to be able to do is when column K of a line is turned to "100 - Purchase Order In", a message to pop up saying something along the lines of "Is the Month In correct?"
Looking through previous answers, I think that this is something along the lines of what I need:
Sub Print_Out()
With Range("d6")
If .Value = "" Then
.Select
MsgBox ("Make sure you enter your surname")
Exit Sub
End If
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
However, I don't know enough about VB Code to know how to change it for my purposes...
I have a column with people(A) and their date of birth(C). I'm looking to find out the number of days when their next birthday is. I searched through here and couldn't find anything. Is there a formula I can use that would calculate this, year after year after year??
View 4 Replies View RelatedI am hopeless at remembering birthdays tbh - so rather than rely on family to remind me, I decided to make a spreadsheet that shows: D.O.B, current age (in years, months, days), and number of days remaining until next birthday.
Please see attached - I can't figure out why the current age calculation is a month out. e.g 'Sebastian' was born on 16 Nov 2008, which makes him 3 months and 11 days old - but '=TODAY()-C16' yields "00 Years 4 Month(s) 12 Days"
Also, 'Leah' has just her birthday - but now where it is supposed to give 'days until next birthday' it gives an error with the formula: '=DATEDIF(TODAY(),EDATE(C4,(YEAR(NOW())-YEAR(C4))*12),"d")'
I have the dates of birth for a load of employees and have been asked to report on all those who will be reaching the age of 62 between 2012 (today) and 31st December 2021.
View 2 Replies View RelatedI have prepared one dash board which having the data for all my employee and clients birthday and anniversaries dates. And i have to check this file and send the E-mail wishes and Mobile messages ( for this we are using SMS country software for bulk messages) .
In column H:H i have birthdays and in B:B i have the name of the employee. so i am using the formula =IF(TEXT(H2,"dd-mm")=TEXT(TODAY(),"dd-mm"),B2,"") to display the Today's Birthday. Now i need to update two things here.
1) Need a formula to display the upcoming birthdays with in a month . for this i am using =IF(TEXT(H2,"mm")=TEXT(TODAY(),"mm"),B2,"")it works well but i need to show the total upcoming birthdays in one place only. suppose the names in B2 ,B10 , B25 having the birthdays in this month it will shows the result in I2 ,I10 , I25 . but i need it to show in one place or in one shape .
2) I need to send Wishes mail to them . Is there any macro to send the wishes mail to those are having the birthday's today ?
On my worksheet I have a date of birth formated yyyy-mm-dd. On my form, I want to be able to identify anyone younger than 16. I tried subtracting from today's date on the worksheet, but that didn't work too well for me.
View 2 Replies View RelatedI have a table with employees' persinal data including birthdays. My aim is to insert an indicating field that would show whether the birthday is gonna come soon.
It shold have several states:
number of days left if the birthday is current or next month
"not soon enough" if the birthday will come in more than 2 months (later than next month);
"happy birthday" if it is today;
"the past weekend" if it was the past weekend and it is Monday today.
What can you suggest?
I use the following fomula to test and advise of an upcoming birthday:-
=IF(AND(MONTH(TODAY())-MONTH(B6)=0,DAY(B6)-DAY(TODAY()) 25.
I am an amateur numerologist (I know what you're thinking), and I'm trying to create a spreadsheet/database worksheet that I can enter birthdays into and it will do the Numerological calculations for me. It may be a fairly complicated thing to set up, but if I could do this, it would help out my hobby a lot.
View 5 Replies View RelatedI am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
i have a data of empl their birthdate wise. i want it to sorting from birth day wise for example first " DAY then Month then year". day come first then month then year. find attched file.
View 3 Replies View RelatedIf a cell contains a birthdate, how do I count the number of days till the next birthday? The date is entered, as an example "1/1/2013".
View 5 Replies View Relatedi have a excel file which contains Name(column A),DOB(column B), & email address(column C),.
i would like to send birthday wishes mail automatically to the people according to their bday.
there may be "n" number of people on a particular day. i would like to send a single mail to all the people.i am using microsoft outlook.
When i open that excel file, it should pop up the New Message window of outlook with all email ids in the To. field with "Happy B'day" as subject and Message body as :"many more happy returns of the day & have a nice n wonderful day ahead".
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
I'm looking for a formula that will return the time left to a birthday, in Month and Days.
View 5 Replies View RelatedWhen speaking with parents I want them to enter in their child's birthday i.e. 12/1/1998 in one cell and then automatically generate what grade they should be in the next cell. If the grade year cutoff date is Sept 1.
View 2 Replies View RelatedI have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies View RelatedI have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
I have a table that includes 2007 data and 2008 data. When I was entering Jan 1 for 2008, it defaulted to 2007. I had the whole table completed with all the 2008 dates before I realized this. Is there a quick way to highlight all the 2008 data and change only the year?
View 9 Replies View RelatedI have a list of african countries and their C02 emissions from 1990 to 2010. The question I'm asked is, who will be the top 5 emitters in the year 2020 given the current trends. I have done a lookup command and compiled a list of the top 5 emitters. My concern is though i do not know how to get the 2020 forecast of the top 5 emitters rather than the current datas.
View 4 Replies View RelatedI am trying to find a formula which will show the date of a potential student on a certain date e.g 1st August 2014
Cell A2 has the students date of birth
Cell B2 has the students current age
I would like cell C2 to show the students date of birth on the 1st August 2014.
I think I may have over engineered this formula.
$F$40 = Cell with Event Date
=IF(WEEKDAY($F$40,2)
Does anyone know a formula that calculates the future value of an unequal payment stream? I am attempting to calculate the value of an investment plan where the periodic payment increases by 1% each period. I can get the answer by using the FV formula for each payment and then adding them up, but would like to be able to do it without having to do so.
View 9 Replies View Related
