Calculation to convert into days

[matthewR]

I have a field (TimePeriod) that has month,day, week or hours. In another column ("Number") is the number. Depending on what it says in the "TimePeriod" column, I need to convert everything into days. What would the calculation be for each of the time periods? Of course "day" time period dosen't need coverted. For example "Time Period" says Month and the "Number" is 5. I need to convert 5 months into days. If "TimePeriod" says "Hours" and "Number" is 120, I need to convert 120 into days. 7 days is considered a week for this.

[HansV]

The number of days that corresponds to 5 months depends on the start date: from January 1, 2013 to June 1, 2013 is 151 days, while June 1, 2013 to November 1, 2013 is 153 days. Would it be OK to take 30 days per month? If so:

[Number]*Choose([TimePeriod]="Month",30,[TimePeriod]="Week",7,[TimePeriod]="Day",1,[TimePeriod]="Hours",1/24)

Instead of 30, you could use 30.4375 (the average number of days per month, taking leap years into account).

If you know the start date, you can calculate the exact number of days from the number of months.

[matthewR]

I used the following:

Number of Days: [Value of Time]*Choose([Unit of Measure]="Months",30.4375,[Value of Time]="Weeks",7,[Value of Time]="Days",1,[Value of Time]="Hours",1/24)

I am getting and #ERROR in the calculation column.

[HansV]

It should be

Number of Days: [Value of Time]*Choose([Unit of Measure]="Months",30.4375,[Unit of Measure]="Weeks",7,[Unit of Measure]="Days",1,[Unit of Measure]="Hours",1/24)

[matthewR]

I noticed what I did wrong and I corrected it but I am still getting the #Error. I pasted what you sent in and it still gives me the #Error.

Some of the rows do not have anything in the Unit of Measure and Value of Time columns. Would that have to be accounted for in the calculation?

[HansV]

Oops, that's my fault. I should have used the Switch function, not the Choose function:

Number of Days: [Value of Time]*Switch([Unit of Measure]="Months",30.4375,[Unit of Measure]="Weeks",7,[Unit of Measure]="Days",1,[Unit of Measure]="Hours",1/24)

[matthewR]

Thanks that worked. When would you use "Choose"?

[HansV]

Choose has an entirely different syntax:

Choose(n, option1, option2, option3, ...)

If n=1, the function will return option1
If n=2, the function will return option2
etc.

The syntax for Switch is

Switch(condition1, option1, condition2, option2, condition3, option3, ...)

Access will evaluate the conditions from left to right until it encounters one that is True; it then stops and returns the corresponding option. So for example if condition1 is False and condition2 is True, the function will return option2.