Free Microsoft Excel 2013 Quick Reference

Date to Calendar

Hello.

I'm having a struggle to find a way to sort out my problem. If you could clarify I would be seriously appreciated.

I have an excel sheet where the whole column A and B are blank, and starting on column C and forwards I have a year calendar.

So far so good.

What I'm trying to find out is the right formula to:

write manually a date on one of the cells in Column A or B and the calendar automatically recognises that cell and highlights the date on the calendar.

I'm sorry if you don't understand I'm trying my best.

Thank you for any help you can give me.

RLory


Post your answer or comment

comments powered by Disqus
Please help...
First off, I know little to nothing about Excel and how this can be done so I am asking for complete example and not just a snippet to work with. PLEASE

Here is my problem:

I have a column that contains a Julian date and I need to create a macro that can be run that will convert that date to a Calendar date. I need it as a macro because the Excel data is updated daily and this column always comes in as a Julian date.

I have found code that will convert the date to calendar date, but I don't know how to get it to work for the entire column or how to make it a macro to can be run as needed.

=DATE(2000+INT(L2/1000),1,MOD(L2/1000))
I need this to work for the entire L column

THANKS IN ADVANCE FOR ALL HELP

How do I Convert a Julian Date to Calendar Date?

Examples: 7365 = 12/31/07

The first number represents the Year and the next 3 numbers are the days of
that year. What formula would I type to get this. Also If I wanted to
subtract the julian date to find out (example when a product will expire)
(7365 - todays date - 180 days = ?) .........(The result would be desired in
this format 06/21/08) Please help!

Please email me with answers at

Is there a way to convert a table that lists events with dates to a calendar
format? For example, I have

Event Date
Seminar 4/15/2005
Posting 5/4/2005
End 7/20/2005

I would then have individual sheets for the months with the items above in
the appropriate boxes in the month calendar. Any help would be appreciated!

Is there any way to utilize the Outlook Calendar/Tickler system for dates
entered into Excel Spreadsheets?

Ideally, I would like to set up a Spreadsheet to trigger a reminder window
similar to the Outlook reminder system for dates I'm putting into
spreadsheets. Or even import the information attached to dates in the
spreadsheet into my Outlook Calendar.

Does anyone have any ideas for this?

I need help converting Julian Date to regular calendar date.
I have from 5000 – 10000 Julian Dates in one column. Ex.
Col A need converted to
4001 = 1 Jan 04
5001 = 1 Jan 05
5030 = 30 Jan 05

Any help would be much appreciated.
Thanks
Ron

--
Ron


Ok, here is what I need. I have a sheet (as attached), that outlines a schedule of payments from a customer. The dates are setup something like this:
Start ---------Finish ---Price Per Month
12/1/2008 - 12/13/2009_ $100
12/14/2009 - 4/15/2010_ $126
4/16/2010 - 5/1/2010_ $0.00
5/2/2010 - 5/1/2014_ $500

All of these numbers must have the ability to be modified.

What I need is to calculate how much to bill them each calendar month. Such as:

Novemer 2008--December 2008 ... December 2009--January 2009
---$100------------$100---------$41.94+73.16------$126.00

Note that, as long as it is a full month, they are charged the full month (ie a month with 28 days is not less expensive than one with 31). However, if the month is split between two payment schedules, it's days determine how much to charge from either plan. Also, the months in the calendar should not be calendar, but manually entered.

I greatly, greatly appreciate your help!!!

Hello everybody.

I'm new to the forums and I appreciate any imput.

I work in a travel agency, the way we keep track of how many people come and go is with a spreadsheet in which we have the client's number, arrival date, departure date, name etc. We manage around 4,000 people each year. What I want to do is find a way to transfer the duration of their trips to a calendar. I'm attaching pictures of both our main lista and the calendar I was thinking about.

Is there a formula or macro that can take those date values and transfer them to the other spread sheet? Or is there any other way in which I can know at any given time, how many people we have in house?

Thanks

Tunk

Hi,

I have attached the sample workbook so that you can go through it.

Following are the purpose of the code I have written

1. Run User Form 'Down Time Calculation' when any cell in column number 20 is selected and 'F9' is pressed.

2. Above user form has four text boxes which will accept 'Date From' ,'Time From' , ' Date To and 'Time To' sequentially.

3. User can enter the date either by typing or by selecting the date from calendar that can be invoked by pressing 'Esc' key.

4. I have user form for calendar and I wish user can activate calendar only by pressing 'Esc' key when they are in 'Date From' or 'Date To' Text box of 'DownTime' Userform. But here I get stuck. I can activate calendar when I press 'Esc' key irresepective of cell selection . However I can not do it when I am in the 'Date From' text box of the 'Down Time Calculation' userform.

How do I activate the calendar userform being in first textbox of 'DownTime' userform and pressing 'Esc' Key

Really appreciate your help

Thanks and Regards

Angshuman

Hi All,

I have a calendar widget that allows a user to choose a date thereby avoiding date format issues. I'm presently using it to set cell values and that works fine since the setting is done from inside the widget.

My problem is that I want to generalize my widget so that it returns a date instead of setting the value of a cell.

In other words:

sub doing_things_that_need_a_date ()
dim mydate as date
mydate= "return value of calendar widget"
'then go do stuff with mydate
end sub

Private Sub OKButton_Click()
mydate = Calendar1.value 'previously this line didn't include mydate and did set the value of the active cell
Unload Me
End Sub

I know that I have the code for the OKButton wrong, I just don't know what to put so that the widget will return a date to the sub from which it is called.

Any thoughts?

-Jen

Hi guys, I'm new to the forum. I currently live in Anchorage, AK. I love messing around with excel but am still in the learning phase. I took this vba from the common questions messages. And I'm trying to tweak it to become my main calendar at work. I was wondering just 1 thing.

How do you black out the past dates?

Like today is september 14th, tomorrow I want september 15th to be highlighted but all dates previous to september 15th to be black.

Here is the code if it helps any one out. Thanks in advance for the help.

	VB:
	
 CreateCalendar() 
    Dim lMonth As Long 
    Dim strMonth As String 
    Dim rStart As Range 
    Dim strAddress As String 
    Dim rCell As Range 
    Dim lDays As Long 
    Dim dDate As Date 
     
     'Add new sheet and format
    Worksheets.Add 
    ActiveWindow.DisplayGridlines = False 
    With Cells 
        .ColumnWidth = 6# 
        .Font.Size = 8 
    End With 
     
     'Create the Month headings
    For lMonth = 1 To 4 
        Select Case lMonth 
        Case 1 
            strMonth = "January" 
            Set rStart = Range("A1") 
        Case 2 
            strMonth = "April" 
            Set rStart = Range("A8") 
        Case 3 
            strMonth = "July" 
            Set rStart = Range("A15") 
        Case 4 
            strMonth = "October" 
            Set rStart = Range("A22") 
        End Select 
         
         'Merge, AutoFill and align months
        With rStart 
            .Value = strMonth 
            .HorizontalAlignment = xlCenter 
            .Interior.ColorIndex = 6 
            .Font.Bold = True 
            With .Range("A1:G1") 
                .Merge 
                .BorderAround LineStyle:=xlContinuous 
            End With 
            .Range("A1:G1").AutoFill Destination:=.Range("A1:U1") 
        End With 
    Next lMonth 
     
     'Pass ranges for months
    For lMonth = 1 To 12 
        strAddress = Choose(lMonth, "A2:G7", "H2:N7", "O2:U7", _ 
        "A9:G14", "H9:N14", "O9:U14", _ 
        "A16:G21", "H16:N21", "O16:U21", _ 
        "A23:G28", "H23:N28", "O23:U28") 
        lDays = 0 
        Range(strAddress).BorderAround LineStyle:=xlContinuous 
         'Add dates to month range and format
        For Each rCell In Range(strAddress) 
            lDays = lDays + 1 
            dDate = DateSerial(Year(Date), lMonth, lDays) 
            If Month(dDate) = lMonth Then ' It's a valid date
                With rCell 
                    .Value = dDate 
                    .NumberFormat = "ddd dd" 
                End With 
            End If 
        Next rCell 
    Next lMonth 
     
     'add con formatting
    With Range("A1:U28") 
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=TODAY()" 
        .FormatConditions(1).Font.ColorIndex = 2 
        .FormatConditions(1).Interior.ColorIndex = 1 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I am trying to return a date using Calendar Control.

I have Calendar1 within frmCalendar.

Code for Calendar1
Code:
Private Sub Calendar1_Click()
    Dim iDate
            iDate = Calendar1.Value
    Unload Me
End Sub
I then have a command button located within worksheets to run another macro. The macro that it calls is call_data:
Code:
Sub call_data()

Dim ws As Worksheet
Dim lRow, fRow, Lines As Long
Dim iDate As Date

Application.ScreenUpdating = False

Set ws = ActiveSheet
lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

cmdDatabaseOpen_Click

Lines = Cells(Rows.Count, 1).End(xlUp).Row - 1

Cells(2, 1).Resize(Lines, 2).Copy _
    ws.Cells(lRow, 2)

ActiveWorkbook.Close

frmCalendar.Show

iDate = Calendar1.Value

fRow = lRow
lRow = fRow + Lines - 1

For i = fRow To lRow
    On Error Resume Next
        Cells(i, 5) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 8, 0)
        Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 3, 0)
        Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 10, 0)
        Cells(i, 8) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 6, 0)
        Cells(i, 9) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 13, 0)
        Cells(i, 11) = iDate
        Cells(i, 13) = Application.WorksheetFunction.SumIf(Range("I5:I" & i), Range("I" & i), Range("C5:C" & i))
        With Cells(i, 15)
            .Formula = "=SUMPRODUCT(--(M" & i & ">" & ws.Name & "_Bands),(M" & i & "-" & ws.Name & "_Bands)," & ws.Name &
"_Diff)"
            .Value = .Value
        End With
        Cells(i, 14) = Cells(i, 15) - Application.WorksheetFunction.SumIf(Range("I5:I" & i - 1), Range("I" & i), Range("N5:N"
& i - 1))
Next

Application.ScreenUpdating = True

End Sub
How do I link iDate in Calendar1 to iDate in call_data?

Best regards
Jon

Hola,

I am working on a project that entails scheduling preventive
maintenance, such as oil changes and inspections, on trucks. I have an
excel spreadsheet data which contains the dates as to when and what type
of preventive maintenance should be scheduled. Now I need to import
that data to calendar (maybe outlook) and then be able to print it for
employees.

Please let me know if this is possible in excel or maybe possible in
access. Thank you so much.

Heng

--
supafreeza
------------------------------------------------------------------------
supafreeza's Profile: http://www.excelforum.com/member.php...o&userid=24774
View this thread: http://www.excelforum.com/showthread...hreadid=383398

Please go easy on me because I'm brand new to Excel. I am trying to find a
way to 'select' a date to populate a cell rather than having to 'type' it in.
Is there a way I can use the Data Validation drop-down function to call up
an interactive calendar from which I can pick a date? If not, is there
another way it can be done?

Gentlemen,

I have a challenging problem. I am preparing a leasing model for tenants in a property. The lease contracts are in rental years and, what I am trying to do is to distribute those rental year figures to calendar year to see an annual income. However the formula I wrote has some flaws.

I have identified the problems with different types of tenants in the file.

PS: For those not familiar with real estate rental year contract, means tenant enters the property at a certain date and pays the same rent until its entry date next year. An example:

Tenant A will pay escalating rents of 5, 10, 15 in its first 3 rental years. Lets assume entry Date is June 20, 2008. Tenant pays 5 until June 20, 2009, 10 Between June 20, 2009 and June 20 2010 and so on.

I am trying to link one sheet with a randomly selected date, to pop up on another sheet, (calendar) and turn a selected color. I'm trying to build something to keep track of times taken for time off from work.

Is this possible?

Hola,

I am working on a project that entails scheduling preventive maintenance, such as oil changes and inspections, on trucks. I have an excel spreadsheet data which contains the dates as to when and what type of preventive maintenance should be scheduled. Now I need to import that data to calendar (maybe outlook) and then be able to print it for employees.

Please let me know if this is possible in excel or maybe possible in access. Thank you so much.

Heng

Hi

I just wanted to thank rebsbails for posting a working solution to my problem and saving me a lot of time, and no thanks to power crazed mods. Why do mods on all forums complain when you ask a question that has already been asked, and yet lock threads when someone posts such a relevant post to old threads that google has already tracked? I never understand

http://www.ozgrid.com/forum/showthread.php?t=20596

To convert backwards-US date to the rest of the world:

=CONCATENATE((IF(MID(B11,4,1)="/",MID(B11,3,1),IF(MID(B11,2,1)="/",MID(B11,3,2),IF(MID(B11,6,1)="/",MID(B11,4,2),MID(B11,4,1))))),"/",IFERROR((IF(SEARCH("/",LEFT(B11,2)),LEFT(B11,1),LEFT(B11,2))),LEFT(B11,2)),"/",RIGHT(B11,4))

Thanks rebsbails cheers

Hi all. I currently import a txt file which is extracted from a seperate system into excel. Most of the dates come through in the australian format(dd/mm/yyyy column d), but for some it converts to mm/dd/yyyy. Not sure why it does this as the system shows the date in the correct format.

Using conditional format (column g) I am trying to show all orders open >0, >30 and >60 days using

	VB:
	
today()-d# 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
. However, the dates converted to mm/dd/yyyy are all showing up as a negative number. I have tried using left, right and mid to extract these dates to convert to the correct format (dd/mm/yyyy).

I have a seperate column (i) which subtracts the date in d from the current date as i want to then use sumproduct in a summary field.

Hi all, I have a Date database(Sheet1) and holiday Database(Sheet2). In cell D2, i have a date(Eg, 22/3/11). I want the date to automatically add itself if it is a saturday or sunday and if any of these date fall on the holidays using VBA macro.

Is it Possible??

Hi Guys

Basically I have a spreadsheet which has lots of features which have a timeline against them.
I want to compare the Actual Date to the Planned Date to show either On time Early Late.
But I want an overall percentage of how many are late, early, on time rather then by each feature.

Please help.

simply -- as example
I want to writ dopwn the Date as 12/12/10---- to be converted to text "Twilves of December two thounsnd and ten --
the point is how I can convert the Date to text as shown above?!!
is that VBA Code or just formula -in XLS ?!!

I want to have a future date (30 days from today’s date) added to a cell in column A only if that cell is already blank and column N has an “O” in that same row. Here is the code that I have right now, and it works to add a value based on the conditions that I just described. I just don’t know how to get it to add a date to that is 30 days in advance of today’s date. Thanks for any help!


	VB:
	
 
    Me.Cells(.Row, "A").Value = "1" 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi
I have a spreadsheet with 15 worksheets.
Each worksheet would be for a vehicle with entries of dates, driver, Congestion charge zone, speed fine, and bus path fine, parking fine amount and Date Paid.
These entries would be entered by hand.
Now I have a Summary page at the start with, Columns of each months date, and rows of the Vehicles linked to the worksheet.
Now I want to create a formula that will apply a conditional format to the month that the driver made one of the 4 mistakes. Thus if in the worksheet of the driver the entree is made for 12/03/05 and there is an entry under speed fine. It must change the cell under the column of March to example green that would indicate a speed fine.
So according to the date the entry is made the summary page on the first worksheet must change. I have made the sheets and all and changed the dates to numbers, and tried to sort it, through elimination, but am struggling.
Any help would be appreciated.
Kind regards
E

Hi there,

I'm just new at learning Excel and Access and i need some information on how to convert date to number in Excel and also how to set and apply a filter that shows only certain records (eg.all dates before 31/1/04) which is in Access.
That would be great if anyone can help :


No luck finding an answer? You could always try Google.